fbpx

Archiwizuj wybrane rekordy tabeli (VBA)

07.03.2019 | Makra 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!

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

Chcesz nauczyć się pisać makra w Excelu?

A jeśli chcesz nauczyć się pisać makra od zera – koniecznie zapisz się na listę zainteresowanych moim kursem wprowadzającym do pisania makr Excel w codziennej pracy cz. 3.! Zapis jest bezpłatny, podobnie jak cotygodniowe artykuły, które lądują prosto na Twojego maila :).

A tutaj wersja wideo:

 

Zapisz się na bezpłatny mailing!

Zapisz się na bezpłatny mailing i otrzymaj bezpłatny e-book „10 najprzydatniejszych trików w Excelu”!

Newsletter Malinowy Excel | Bezpłatny e-book
Przeczytaj podobne wpisy

Kategorie

31 komentarzy

  1. rafal

    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?

    Odpowiedz
    • Malina

      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
      🙂

      Odpowiedz
  2. Marek

    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?

    Odpowiedz
    • Malina

      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ć ;(.

      Odpowiedz
  3. MarciN

    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

    Odpowiedz
    • Malina

      Marcin, bardzo się cieszę :). Spróbuj Arusz_w_ktorym_ma_byc_ukrycie.Rows(ActiveCell.Row).hidden = true
      🙂

      Odpowiedz
  4. bartek

    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

    Odpowiedz
    • Malina

      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?

      Odpowiedz
      • bartek

        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.

        Odpowiedz
        • Malina

          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 🙂

          Odpowiedz
  5. Slawek

    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

    Odpowiedz
    • Malina

      Aby kopiowana linijka była dodatkowo wycinana, należałoby dopisać taką linijkę: Rows(Wiersz).Delete 🙂

      Odpowiedz
  6. Krzysztof

    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

    Odpowiedz
    • Malina

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

      Odpowiedz
      • Piotr

        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ę!

        Odpowiedz
        • Malina

          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?

          Odpowiedz
          • Piotr

            dokładnie o to chodzi, jak będzie np 15 to nic sie nie robi tylko wyskakuje komunikat że osiągnąłem max liczbę da się dalej wprowadzać dane, jeśli nie ma 15 to nic się nie wyświetla. dane mają być kasowane po usunięciu x ale tylko te dane mają być usuwane, przy których odznaczyliśmy tego x, ponowne postawienia x przy tych dany wkleja je ponownie przepraszam jakoś to tłumaczę na około hehehe

  7. Martyna

    Cześć! Poszukuję podpowiedzi – potrzebuję wykonać formularz, którego dane byłyby zapisywane do tabeli. Wyglądałoby to trochę inaczej niż tutaj, ponieważ formularz miałby jakiś określony wygląd i z niego komórki byłyby za pomocą przycisku zapisywane w tabeli, w której później można byłoby filtrować dane. Natomiast potrzebne jest, żeby następnie po numerze ID można było wczytać dany formularz (wyobrażam sobie, że w innym arkuszu musiałoby się to dziać), który można byłoby edytować, ponieważ dane byłyby uzupełniane na raty. I następnie znów należałoby je zapisać- ale tak, żeby nie było duplikatów ID, gdzie w jednym wierszu są dane A B C a w drugim np. A B C D.
    Czy mogłabym prosić o pomoc? 🙂
    A może dałoby radę stworzyć osobny post na ten temat? 🙂

    Odpowiedz
    • Malina

      Hej! A może zamiast angażować w to VBA, użyć wbudowanego formularza Excela? On co prawda ma narzucony wygląd, ale właśnie to robi: dodaje wiersze i można dzięki niemu też edytować. Odnośnie duplikatów ID, to można też zastosować wbudowane formatowanie warunkowe, które zaznacza duplikaty.
      Może tak?

      Odpowiedz
  8. Makro suma tabel

    Dzień dobry 🙂 szukam makra, które będzie zbierało dane z kilku identycznych tabel do jednej tabeli. Myślę, ze to makro się świetnie nada z małymi modyfikacjami. Mogłabym prosić o pomoc? Każdy pracownik ma swój arkusz a przełożony powinien mieć widok jednej tabeli składającej się ze wszystkich tabel. Tabele pracowników mają identyczny widok. Z góry bardzo dziękuję!

    Odpowiedz
    • Malina

      A może zastosować do tego Power Query? Byłoby łatwiej 🙂

      Odpowiedz
      • edyta

        czy można zmodyikować tak to makro, żeby dane zaczytywały się z kilku takich samych arkuszy. Przygotowuję kartę pracy i każdy z pracowników będzie miał swój arkusz. Makro które Pani zastosowała jest super, ale działa tylko dla jednego arkusza. Mogę prosić o pomoc?

        Odpowiedz
        • Malina

          Trzeba byłoby zdarzenie podpiąć do tych arkuszy 🙂

          Odpowiedz
  9. AniaK

    Pani Malino,
    dziękuję za – kolejne – bardzo pożyteczne rozwiązanie, które bardzo ułatwi mi pracę. Mam też pytanie – jak wyglądałby kod gdybym chciała stworzyć kilka dodatkowych arkuszy, np Hist1, gdzie byłyby przenoszone dane po wpisaniu w kol. H -„a”, Hist2, po wpisaniu „b”, itd? Pozdrawiam serdecznie i jeszcze raz dziękuję

    Odpowiedz
    • Malina

      Dzień dobry,
      w takiej sytuacji ja bym tworzyła 2 zmienne dla miejsca docelowego (KomPusta) i do nich wklejała. Będą to komórki o tym samym adresie, tylko w innych arkuszach (Hist1, Hist2). I wtedy można byłoby kopiować dane np. tak:

      Range(„A” & Wiersz).Copy KomPusta1

      Zakładając, że interesuje nas tylko ta jedna komórka, która ma być przenoszona do Hist1 🙂

      Odpowiedz
  10. Andrzej

    Rewelacyjna wiedza. Gratuluję. Proszę wybaczyć „zielonemu”: jak skopiować rekordy do innego skoroszytu? Mam kilka skoroszytów, w nich po 2 arkusze. Chciałbym to wszystko zgromadzić w jednym arkuszu w odrębnym skoroszycie.
    Pozdrawiam.

    Odpowiedz
    • Malina

      Bardzo się cieszę i dziękuję :).
      Odnośnie pytania – ja bym tutaj:
      Set ArkHist = Sheets(„Hist”)
      Dorzuciła jeszcze plik, do którego dane mają być kopiowane. Musi być otwarty. Czyli
      Set PlikArchiwum = Workbooks(tutaj_ścieżka_i_nazwa_pliku).open
      Set ArkHist = plikarchiwum.Sheets(„Hist”)

      Coś na ten kształt 🙂

      Odpowiedz
  11. Kamil

    Dzień dobry,
    Dziękuję za tą lekcję, jest ona bardzo cenna. Robi Pani wielką rzecz dzieląc się wiedzą.
    Ciekawi mnie jeszcze, co należałoby dopisać do kodu aby w arkuszu historycznym dopisywać jeszcze obok datę przeniesienia (wstawienia) archiwizowanego wiersza. Mogłaby Pani pomóc?
    Pozdrawiam

    Odpowiedz
    • Malina

      Bardzo się cieszę, że Panu się podoba 🙂
      Odnośnie pytania, proponuję coś takiego (do przetestowania, bo piszę na szybko:)):
      KomPusta.offset(0,1).value = Date
      🙂

      Odpowiedz
  12. Blasius

    A co zrobić w przypadku kopiowania nie od A 1, a np od A 1155?
    Jak zamienię wartości , to pojawia się tylko 1 wiersz, a zmieniający się po dodawaniu kolejnych plusów…

    Odpowiedz
  13. Blasius

    Witam,
    napisałem do Pani pytanie dzisiaj około godz.13-tej i ono… zniknęło!
    To napiszę jeszcze raz:
    Jak zmodyfikować kod, aby można było zmienić pierwszą komórkę, do której się kopiuje z A1 na powiedzmy X3402?

    Odpowiedz

Odpowiedz Malina Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest