Czyli korespondencja seryjna w Excelu
Wyobraźmy sobie sytuację, że chcemy wszystkim pracownikom dać 10% podwyżki (lubię sobie wyobrażać takie sytuacje:)). Wszystko cudownie, ale wymaga to od nas stworzenia całej masy papierków, między innymi pisemną informację dla pracownika o tym fakcie. Chcemy mieć taką informację wydrukowaną na papierze, no więc nic prostszego – idealna sytuacja dla korespondencji seryjnej w Wordzie. Ale… dostaliśmy od przełożonej jedyną słuszną formatkę, tylko, że… w Excelu. Ma być taka i koniec.
No i zonk: jak zrobić korespondencję seryjną w Excelu???
Na szczęście jest rozwiązanie, tylko wymaga napisania makra.
Plik, w którym będziemy działać składa się z 2 arkuszy:
- Wniosek
- Dane
W pierwszym z nich – Wniosek – znajduje się formatka do zadania:
W drugim zaś – Dane – dane pracowników, którymi będą zasilane żółte pola we wniosku. Dane te umieściłam w tabeli o nazwie tbOsoby:
Aby rozwiązać problem korespondencji seryjnej, wymyśliłam, że będę tworzyć oddzielne arkusze dla każdego pracownika z odpowiednimi jego danymi w żółtych komórkach:
- F9 to imię i nazwisko,
- F12 to nowe wynagrodzenie.
Czyli zadaniem makra będzie skopiowanie arkusza, nazwanie go imieniem i nazwiskiem pracownika oraz wpisanie danych pracownika w żółte komórki. Tyle.
Omówienie kodu
Jak już logikę znamy, to przejdę do omówienia kodu.
Cała procedura (sub) nazywa się Korespondencja. Zaraz potem następuję zdefiniowanie zmiennych i wyłączenie odświeżania ekranu (żeby okienka nie mrugały). Odpowiada za to tenkod:
Sub Korespondencja() Dim Zakres As Range, Czlowiek As String, Stawka As Double Dim ArkWniosek As Worksheet, ArkNowy As Worksheet Dim Licznik As Long, Wierszy As Long Application.ScreenUpdating = False
Następnie jest przypisanie zakresów do zmiennych (oczywiście wszelkie nazwy arkuszy czy tabel muszą być takie same, jak w arkuszu – uważaj na to) i od razu policzenie dla ilu osób powinien się stworzyć nowy arkusz. Odpowiada za to zmienna Wierszy, ponieważ jest to tak na prawdę liczba wierszy w tabeli z imionami i nazwiskami (tbOsoby):
Set ArkWniosek = Sheets("Wniosek") Set Zakres = Sheets("Dane").Range("tbOsoby") Wierszy = Zakres.Rows.Count
Kolejny etap to już pętla For, która przelatuje po tabeli tbOsoby i w pierwszym kroku przypisuje do zmiennych Czlowiek i Stawka wartości kolejno z niej odczytane:
For Licznik = 1 To Wierszy Czlowiek = Zakres.Cells(Licznik, 1).Value Stawka = Zakres.Cells(Licznik, 2).Value
Dalej następuje kopiowanie arkusz z wnioskiem (skopiowany arkusz to od tej pory ArkNowy i UWAGA! Nazwy tych arkuszy muszą być unikalne – jak każdego arkusza w Excelu!) i zmiana jego nazwy na imię i nazwisko pracownika, oraz wpisanie odpowiednich wartości do żółtych komórek:
ArkWniosek.Copy After:=ArkWniosek Set ArkNowy = Sheets(ArkWniosek.Index + 1) ArkNowy.Name = Czlowiek ArkNowy.Range("F9").Value = Czlowiek ArkNowy.Range("F12").Value = Stawka Next
Na koniec to już tylko aktywowanie głównego arkusza Wniosek i włączenie odświeżania ekranu (żeby okienka mrugały):
ArkWniosek.Activate Application.ScreenUpdating = True End Sub
Kod w całości wygląda tak:
Sub Korespondencja() Dim Zakres As Range, Komorka As Range, Czlowiek As String, Stawka As Double Dim ArkWniosek As Worksheet, ArkNowy As Worksheet Dim Licznik As Long, Wierszy As Long Application.ScreenUpdating = False Set ArkWniosek = Sheets("Wniosek") Set Zakres = Sheets("Dane").Range("tbOsoby") Wierszy = Zakres.Rows.Count For Licznik = 1 To Wierszy Czlowiek = Zakres.Cells(Licznik, 1).Value Stawka = Zakres.Cells(Licznik, 2).Value ArkWniosek.Copy After:=ArkWniosek Set ArkNowy = Sheets(ArkWniosek.Index + 1) ArkNowy.Name = Czlowiek ArkNowy.Range("F9").Value = Czlowiek ArkNowy.Range("F12").Value = Stawka Next ArkWniosek.Activate Application.ScreenUpdating = True End Sub
W wyniku działania makra otrzymujemy tyle nowych arkuszy, ilu było pracowników na liście. Teraz, jeśli ich wszystkich chcemy wydrukować (nieistotne, czy na papierze, czy do PDF), wystarczy zaznaczyć te arkusze, które chcemy drukować i wybrać odpowiednią opcję drukowania (omawiam to niżej).
To, że że każdego mamy w oddzielnym arkuszu może Wam się wydać przerażające, na szczęście Excel umożliwia nam łatwą nawigację po arkuszach. Służą do tego strzałki obok kart arkuszy (wersja dla Excela 2013 w górę):
W Excelu 2007 i 2010 wygląda to nieco inaczej:
W Excelu 2013 i wyższych, aby przejść do ostatniego arkusza trzeba przycisnąć klawisz Ctrl i nacisnąć strzałkę w prawo (jak na rysunku), a w niższych wersjach jest do tego dedykowana strzałeczka: z pionową kreską (coś jak przewijanie filmu).
Analogicznie sytuacja wygląda z nawigowaniem do pierwszego arkusza.
Dodatkowo, żeby zaznaczyć arkusze od – do, należy:
- wskazać pierwszy z nich (w tym przypadku będzie arkusz dla pierwszej osoby, zaraz po arkuszu Wniosek),
- przytrzymać klawisz Shift,
- zaznaczyć ostatni arkusz.
A jeśli chcecie zaznaczać tylko wybrane arkusze – wskazujecie je i jednocześnie trzymacie klawisz Ctrl.
A więc jak już zaznaczyliśmy te arkusze, które chcemy drukować – kwestia odpowiednich ustawień drukowania (skrót klawiszowy do drukowania to Ctrl + p, lub Plik/Drukuj):
Numer 2 na obrazku powyżej o wybór drukarki (ja wybrałam druk do PDF), a nr 3 to właśnie opcja Drukuj aktywne arkusze, którą trzeba zaznaczyć, aby wydrukować wszystkie zaznaczone arkusze na raz. Kocham tę opcję, zwłaszcza, że jest domyślna :).
No i tyle. Drukowania nie wrzucałam do makra, ponieważ pomyślałam, że wolę zostawić Wam dowolność w sposobie drukowania, jednocześnie zachowując prosty kod.
Jeśli chcesz zobaczyć krok po kroku jak napisać kod, polecam film:
A gotowiec do pobrania tutaj:
MalinowyExcel_Korespondencja seryjna w Excelu dw
Znowu genialna Malina ratuje mi życie! I czas:)
O rety, mega się cieszę 🙂
Malino, rewelacja.
A kiedy kilkudniowy kurs z VBA ?
Ewa
Hej! Cieszę się, że Ci się podoba 🙂
Najbliższy termin szkolenia stacjonarnego z VBA jest 24-26 czerwca 2019 w Warszawie 🙂
Czemu można wpisać tylko 8 pozycji w danych ?
Hej, można wpisać więcej. Jest to tabela, a więc zakres się automatycznie powiększa. Jeśli się nie da – może arkusz jest chroniony?
Cześć,
za każdym razem jak próbuje uruchomić makro to co prawda arkusze się tworzą, ale wyskakuje mi przy tym alert o błędzie.
Run-time erroe ‘1004’
Method ‘Name’ of object’_worksheet’ failed
i przy naciśnięciu debug zaznacza mi na żółto linijke ArkNowy.Name = Czlowiek
Wiesz może o co może tu chodzić?
Jeszcze jedno pytanko. Za każdym razem tworzy mi się również arkusz podpisany “Wniosek (2)”
Nie wiem jak się tego pozbyć
PS. Tak w ogóle to super makro 🙂
Hmmm… wygląda to tak, jakby wartość zmiennej “Czlowiek” zawierała np. jakieś niedozwolone znaki, była za długa albo w inny sposób nie odpowiadała Excelowi. Zobacz proszę jakie są wartości tej zmiennej.
Ok, już wiem o co chodziło. Problem pojawiał się w momencie, gdy miałem w tabeli puste wiersze, np po wykasowaniu danych z ostatniego wiersza. Teraz już jest wszystko ok. Mam jeszcze jedno pytanko. Tworzę coś na podstawie przedstawionego pliku, tylko mam taki problem, że plik będzie wykorzystywany przez wiele osób, dostępem z dysku sieciowego. Każda z tych osób będzie wprowadzała swoje dane i uruchamiala makro. Przy 1 osobie nie ma zadnego problemu, ale druga i kolejne przy naciśnięciu na makro, dodadzą arkusze ze swoimi danymi, oraz zdublują arkusze dodane już wcześniej przez osoby które wcześniej uzupełniły dane. Na początku wprowadziłem rozwiązanie aby po naciśnięciu przycisku usuwaly się arkusze po za tymi w których trzymam dane i dopiero wtedy przechodzi do polecenia dot. korespondencji seryjnej, dzięki czemu nie ma duplikatow, ale to też nie jest dla mnie dobre, ponieważ w utworzonych arkuszach mogą być wprowadzone pewne komentarze które się automatycznie usuną po takim zabiegu. Czy jest jakiś prosty sposób na to aby makro sprawdzało, czy już istnieje arkusz o podanej nazwie przed utworzeniem, i tworzyło tylko te “nowe”?
Hmmm, chyba nie do końca wiem, o co chodzi… Makro nie utworzy dwóch arkuszy o takiej samej nazwie ;(
Dzięki za poradnik. Bardzo przydatne makro. Ma tylko jedno pytanie. Część rozumiem jak działa ale nadal jestem chyba za głupi. Mianowicie chodzi mi o fragment “ArkWniosek.Copy After:=ArkWniosek”. Chciałbym aby nowe arkusze generowały się po arkuszu “Dane”. Nie bardzo wiem jak zmusić makro żeby generowało nowe arkusze za ostatnim arkuszem.
Hej :). Jeśli ma być za ostatnim arkuszem, to niestety przed każdym wstawieniem arkusza trzeba policzyć ile tych arkuszy jest i wstawić za ostatnim ;(. Czyli do liczenia arkuszy będzie: Sheets.Count, i potem After:=Sheets(x). Czyli np: sheets(1).copy after:=sheets(sheets.Count)
Witaj
Mam problem w korespondencji seryjnej.
Na bazie filmu Maliny „Korespondencja seryjna w Excelu„
https://www.youtube.com/watch?v=Tfj1aB6nusQ
Stworzyłem makro dla protokołu okresowego przeglądu sprzętu sportowego.
Dane w protokole uzupełniają się automatycznie z tabeli skoroszytu DANE
Jednak tylko raz gdy po raz pierwszy uruchamiam makro
Makro generuje zadaną liczbe protokołow i zasysa dane ze skoroszytu.
Jednak robi to tylko RAZ…. W przypadku zmiany wartości w komórkach protokołu DANE
Kolejne naciśniecie przycisku „uruchom makro”
Powoduje komunikat o błędzie „1004” ta nazwa jest już zajeta
Chciałbym aby po zmianie danych w skoroszycie DANE makro nie generowało już kolejnych protokołów, ale zmieniło dane w już istniejacych
Sub korespondencja()
Dim zakres As Range, czasza As String
Dim arkProtokół As Worksheet, arknowy As Worksheet, arkdane As Worksheet
Dim wiersze As Long, licznik As Long
Application.ScreenUpdating = False
Set arkProtokół = Sheets(“PROTOKÓŁ”)
Set zakres = Sheets(“dane”).Range(“sprzęt”)
wiersze = zakres.Rows.Count
For licznik = 1 To wiersze
numersprzetu = zakres.Cells(licznik, 1)
pilka1 = zakres.Cells(licznik, 2)
pilka2 = zakres.Cells(licznik, 3)
arkProtokół.Copy after:=arkProtokół
Set arknowy = Sheets(arkProtokół.Index + 1)
arknowy.Name = numersprzetu
arknowy.Range(“A1”).Value = numersprzetu
arknowy.Range(“I5”).Value = piłka1
arknowy.Range(“I4”).Value = piłka2
Next
arkProtokół.Activate
Application.ScreenUpdating = True
End Sub
przepraszam w wierwszym pytaniu wkradl sie bład zmiany dokonywane sw w skoroszycie DANE nie w protokole
Cześć 🙂
Mam pytanie dotyczące zakresu danych (tabela). W moim przypadku zakres danych jest dynamiczny (ilość wierszy jest różna każdego dnia). Czy w tej sytuacji powyższy kod również zdziała, czy trzeba coś zmienić w definicji zmiennych?
Bardzo dziękuję za odpowiedź 🙂
Hej 🙂
Tabela, z której korzystam w moim rozwiązaniu też jest dynamiczna. Czyli jak wstawisz do niej więcej/mniej danych – zadziała :).
I jak? Udało się?