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
Ponieważ lubię prostotę, to na początek jednak założenia:
- Znak „x” wstawiamy w kolumnie H (Wykonane (x))
- Kopiujemy dane do arkusza Hist, w którym jest już nagłówek tabeli, zaczynający się w A1
- 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:
- określenie arkusza historycznego
- określenie zakresu dotychczasowych danych historycznych, a tym samym:
- 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:
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?
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
🙂
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?
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ć ;(.
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
Marcin, bardzo się cieszę :). Spróbuj Arusz_w_ktorym_ma_byc_ukrycie.Rows(ActiveCell.Row).hidden = true
🙂
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
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?
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.
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 🙂
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
Aby kopiowana linijka była dodatkowo wycinana, należałoby dopisać taką linijkę: Rows(Wiersz).Delete 🙂
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
Hej, pytanie na jakiej podstawie program miałby wiedzieć, które x są zbędne/omyłkowe? Od tego wszystko zależy.
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ę!
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?
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
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? 🙂
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?
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ę!
A może zastosować do tego Power Query? Byłoby łatwiej 🙂
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?
Trzeba byłoby zdarzenie podpiąć do tych arkuszy 🙂
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ę
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 🙂
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.
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 🙂
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
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
🙂
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…
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?