fbpx

Zawiadomienie pracowników o zmianie wynagrodzenia

22.08.2017 | ECP3, HR, Makra VBA, Wynagrodzenie

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.

Wynik

Plik, w którym będziemy działać składa się z 2 arkuszy:

  1. Wniosek
  2. Dane

W pierwszym z nich – Wniosek – znajduje się formatka do zadania:

Formatka

Formatka wniosku

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:

Tabela z danymi zasilającymi wniosek

Tabela z danymi zasilającymi wniosek

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ę):

Strzałki nawigacji między arkuszami

Strzałki nawigacji między arkuszami (wersja 2013 i wyższe)

W Excelu 2007 i 2010 wygląda to nieco inaczej:

Strzałki nawigacji między arkuszami 2010

Strzałki nawigacji między arkuszami 2007 i 2010

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:

  1. wskazać pierwszy z nich (w tym przypadku będzie arkusz dla pierwszej osoby, zaraz po arkuszu Wniosek),
  2. przytrzymać klawisz Shift,
  3. 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):

Ustawienia drukowania

Ustawienia drukowania

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:

Jeżeli chcesz lepiej poznać makra i VBA zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 3! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

16 komentarzy

  1. Znowu genialna Malina ratuje mi życie! I czas:)

    Reply
    • O rety, mega się cieszę 🙂

      Reply
  2. Malino, rewelacja.
    A kiedy kilkudniowy kurs z VBA ?
    Ewa

    Reply
    • Hej! Cieszę się, że Ci się podoba 🙂
      Najbliższy termin szkolenia stacjonarnego z VBA jest 24-26 czerwca 2019 w Warszawie 🙂

      Reply
  3. Czemu można wpisać tylko 8 pozycji w danych ?

    Reply
    • 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?

      Reply
  4. 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 🙂

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

      Reply
      • 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”?

        Reply
        • Hmmm, chyba nie do końca wiem, o co chodzi… Makro nie utworzy dwóch arkuszy o takiej samej nazwie ;(

          Reply
  5. 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.

    Reply
    • 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)

      Reply
  6. 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

    Reply
  7. przepraszam w wierwszym pytaniu wkradl sie bład zmiany dokonywane sw w skoroszycie DANE nie w protokole

    Reply
  8. 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ź 🙂

    Reply
    • 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ę?

      Reply

Leave a Reply to Malina Anuluj pisanie odpowiedzi

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

Pin It on Pinterest