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

6 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

Dodaj komentarz

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