• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Archiwizuj wybrane rekordy tabeli (VBA)

Czyli jak makra mogą ułatwić nam życie

Często (jak nie zawsze!), gdy wykonujemy powtarzającą się czynność, zaczyna nam się ona nużyć i denerwuje nas, że ją po raz kolejny wykonujemy. I to niezależnie od tego, czy jest to bardzo prosta czynność, czy skomplikowana. Powiem wręcz tak: im łatwiejsza ta czynność, tym bardziej zaczyna nas denerwować i chcemy, żeby działa się sama. I tak np., gdy chcemy piszemy formułę, którą potem chcemy skopiować, po zatwierdzeniu jej Enterem – zaznaczenie schodzi do komórki niżej. Aby więc skopiować tę formułę – musimy kliknąć na komórkę wyżej, a dopiero potem kopiować. I to ponowne zaznaczanie, ten jeden dodatkowy klik, maksymalnie działa nam na nerwy! BTW, wystarczy zatwierdzić wpis Ctrl + Enter, to zostaniemy w tej samej komórce ;).

Podobnie jest z nieco bardziej skomplikowanymi tematami. Załóżmy, że prowadzimy rejestr obsługiwanych zamówień i chcemy archiwizować te zrealizowane. W danych mamy ostatnią kolumnę Wykonane (x) (H), w której wpisujemy znak “x”, co ma oznaczać, ze dane zamówienie jest już zrealizowane. Następnie rekord tego zamówienia kopiujemy do archiwum, czyli arkusza Hist w tym samym pliku (można byłoby się pokusić od razu o usuwanie takiego rekordu ;)). Dane kopiowane są do pierwszego wolnego wiersza.

Prosta czynność, która może zostać z łatwością zautomatyzowana. I tym właśnie zajmę się w tym artykule. Napiszę makro mające na celu archiwizowanie danych, czyli z tabeli w arkuszu Dane będzie kopiowało rekordy do pierwszego wolnego wiersza arkusza Hist. Efekt będzie taki:

Do dzieła!

Chcesz się nauczyć makr w Excelu? Zacznij od nagrywania!

Formatka wygląda tak:

Formatka

Formatka

Ponieważ lubię prostotę, to na początek jednak założenia:

  1. Znak “x” wstawiamy w kolumnie H (Wykonane (x))
  2. Kopiujemy dane do arkusza Hist, w którym jest już nagłówek tabeli, zaczynający się w A1
  3. Kopiujemy cały wiersz danych, bez kolumny Wykonane (x)

Makro ma się uruchamiać “samo”, gdy użytkownik wpisze do kolumny H wartość “x”. Musi więc to być zdarzenie arkusza OnChange. O tym, jak je wywołać pisałam tutaj.

Na początku deklarujemy zmienne, obsługujemy błędy i wyłączamy odświeżanie ekranu (unikniemy jego “mrugania”) i określamy wiersz i kolumnę zmienionej przed chwilą komórki (Target):

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ZakresHist As Range, Wiersz As Long, Kolumna As Long, KomPusta As Range
    Dim ArkHist As Worksheet
    
    On Error GoTo Koniec

    Wiersz = Target.Row
    Kolumna = Target.Column
    
    Application.ScreenUpdating = False
    
Koniec:
    Application.ScreenUpdating = True
End Sub

Ponieważ makro uruchomi się na zmianę jakiejkolwiek komórki arkusza, musimy sprawdzać czy user zmienił komórkę w kolumnie H i czy wpisał tam “x”. Dopiero wtedy skopiujemy zatwierdzony  rekord i wkleimy do arkusza historycznego. If wygląda tak:

    If Kolumna = 8 And Target.Value = "x" Then
        
    End If

A w środku tego ifa będzie cała praca:

  1. określenie arkusza historycznego
  2. określenie zakresu dotychczasowych danych historycznych, a tym samym:
  3. określenie pierwszej komórki w wierszu pod nim

Oto kod, który to robi:

    If Kolumna = 8 And Target.Value = "x" Then
        'określanie pierwszego wolnego wiersza historii. Zał: od A1
        Set ArkHist = Sheets("Hist")
        Set ZakresHist = ArkHist.Range("A1").CurrentRegion
        Set KomPusta = ArkHist.Range("A" & ZakresHist.Rows.Count + 1)
    
        Range("A" & Wiersz, "G" & Wiersz).Copy KomPusta
    End If

Całość wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ZakresHist As Range, Wiersz As Long, Kolumna As Long, KomPusta As Range
    Dim ArkHist As Worksheet
    
    On Error GoTo Koniec
    
    Application.ScreenUpdating = False
    
    Wiersz = Target.Row
    Kolumna = Target.Column
        
    If Kolumna = 8 And Target.Value = "x" Then
        'określanie pierwszego wolnego wiersza historii. Zał: od A1
        Set ArkHist = Sheets("Hist")
        Set ZakresHist = ArkHist.Range("A1").CurrentRegion
        Set KomPusta = ArkHist.Range("A" & ZakresHist.Rows.Count + 1)
    
        Range("A" & Wiersz, "G" & Wiersz).Copy KomPusta
    End If
    
Koniec:
    Application.ScreenUpdating = True
End Sub

Makro będzie kopiowało dane niezależnie od tego, jakie będą w nim dane. Aby tak się nie działo i aby kopiowane były dane tylko wtedy, gdy dane są z “tabeli” – należałoby wprowadzić pewne modyfikacje w kodzie. Myślę jednak, że jest to nie potrzebne, i że użytkownicy będą korzystali z makra świadomie. A jak nie, to najwyżej oczyści się historię :). Uważam, że lepiej napisać prostszy kod, niż bawić się w naprawianie świata i przewidywanie wszelkich możliwych problemów.

Ważna rzecz: zapisz plik jako plik z obsługą makr, czyli z rozszerzeniem .xlsm lub .xlsb. Wszystko powinno śmigać :).

 

Tutaj możesz pobrać plik z gotowym rozwiązaniem:

MalinowyExcel Archiwizuj wybrane rekordy dw.zip

 

A tutaj wersja wideo (pojawi się wkrótce):

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Tagi , , , , , , , , .Dodaj do zakładek Link.

16 odpowiedzi na „Archiwizuj wybrane rekordy tabeli (VBA)

  1. rafal mówi:

    Bardzo fajne VBA, Super !!!
    Mam pytanie czy do arkusza Hist można kopiować wartości jeżeli w arkuszu Dane były używane formuły do obliczania?

    • Malina mówi:

      Hej, cieszę się, że Ci się przyda :). Jeśli chodzi o kopiowanie formuł – ja bym w tedy wklejała specjalnie wartości, żeby tych formuł się pozbyć. Czyli:
      Range(“A” & Wiersz, “G” & Wiersz).Copy
      KomPusta.PasteSpecial xlPasteValues
      🙂

  2. Marek mówi:

    Cześć,
    Super rozwiązanie, czegoś takiego właśnie potrzebuję. Czy jest jakiś sposób, żeby “wyzwalaczem” kopiowania był wynik formuły, czyli znak “X” pojawia się w zależności od wartości komórki obok?

    • Malina mówi:

      Hej, super! Odnośnie wyzwalacza, hmmm…. może tak, że np. przy zapisywaniu pliku uruchomi się skrypt, który będzie przenosił komórki z tym znakiem do innego arkusza? Bo tak po prostu na wynik formuły to nie wiem jak to zrobić ;(.

  3. MarciN mówi:

    Witam;
    Co należy dopisać do kodu, jeżeli chciałbym aby po wpisaniu “x” automatycznie ukryło wiersz który został przeniesiony do Arkusza “hist”.
    Dziękuję:)
    mega przydatna stronka,
    Pozdrawiam

  4. bartek mówi:

    Witaj,

    czy można zmodyfikować to makro aby przenosiło dane do nowego arkusza za pomocą przycisku i od razu sprawdziło czy już taki wiersz nie istnieje?. Myślę o tworzeniu bazy klientów i stałą aktualizację. Chciałbym przenosić je przyciskiem z pliku roboczego.

    pozdrawiam serdecznie

    • Malina mówi:

      Hej, czyli chodzi o to, aby dane przenosiły się też między plikami? Z roboczego do docelowej bazy?
      A czy te wiersze mają jakiś identyfikator, czy duplikaty trzeba sprawdzać po kombinacji wszystkich kolumn jednocześnie?

      • bartek mówi:

        Witaj,
        podobnie jak tutaj chodzi mi o przenoszenie danych z wiersza z arkusza roboczego do drugiego arkusza w tym samym pliku.
        Mają to być dane klienta więc należałoby sprawdzić czy już te dane występują i w takim przypadku zgłosić błąd lub pominąć kopiowanie aby nie dublować identycznych wierszy.

        • Malina mówi:

          Hej, zobacz komentarz Sławka i moją odpowiedź – może to będzie rozwiązanie? Założeniem w tym rozwiązaniu było to, że jak już jest postawiony “x” przy jakimś rekordzie, tzn., że został on już skoiowany.

          Ja natomiast myślę o innym rozwiązaniu dla tego problemu: formatowanie warunkowe, które sprawdzałoby takie duble. Myślę, że będzie łatwiej i sprawniej 🙂

  5. Slawek mówi:

    Pani Malino
    sprawdziłem w pliku jeśli znaczę x to się przepisze
    i odznaczę
    jeszcze raz zaznaczę to się powtórnie skopiuje do archiwum
    i mamy dublet !!!!
    czy nie lepiej aby po zaznaczeniu”x” skopiował się do archiwum
    i następnie kasował ze skoroszytu dane
    jest to bardziej bezpieczne
    JAK WTEDY wyglądało by to VBA??
    pozdrawiam
    Slawek

  6. Krzysztof mówi:

    Po wstawieniu iluś tam x-ów (rekordów) może się okazać, że niektóre są zbędne lub omyłkowo wstawione.
    Pytanie: czy jest opcja usunięcia zbędnych lub omyłkowych rekordów

    • Malina mówi:

      Hej, pytanie na jakiej podstawie program miałby wiedzieć, które x są zbędne/omyłkowe? Od tego wszystko zależy.

      • Piotr mówi:

        Podepnę się do pytania. Dodam, że jestem całkowitym laikiem jeśli chodzi o excela nie korzystam z niego często /ok bardzo mało :)/, a chciałem sobie wykonać taki plik jak Pani podała z dodatkowymi 3 opcjami: 1. po odznaczeniu “x” w pierwszej tabeli wpis będzie kasowany z listy /historycznej/ do której skrypt go skopiował; 2. po zaznaczeniu np. 15 “x” na liście /z listy powiedzmy 200 pozycji w 1 tabeli z danymi/ pojawi się komunikat, że “Zaznaczyłeś MAX ilość x = 15” przy założeniu że możemy sobie zmieniać tą wartość; 3. opcja nie musi być konieczna- po zaznaczeniu “x” cały wiersz zmienia kolor na inny po odznaczeniu wraca do poprzedniego dodam, że opcja 3 nie jet mi koniecznie potrzebna. Za pomoc, Bardzo Dziękuję!

        • Malina mówi:

          Hej, to ja od końca:
          3. tutaj można zastosować formatowanie warunkowe (bez makra)
          2 i 1. Cyz to ma działać tak, że dane mają być kasowane np. po naciśnięciu przycisku? I jak będzie 15 x-ów, to ma się wyświetlać komunikat? Jak jest 15 to co wtedy? A co, gdy nie ma?

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *