• 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.

12 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

Dodaj komentarz

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