Praktycznie każdy analityk na co dzień wykorzystuje tabele przestawne do szybkiej analizy danych i przygotowywania zestawień. Analizując dane wklikujemy się w poszczególne komórki tabeli, aby zobaczyć szczegóły składające się na daną wartość. To przekłada się na generowania dużej liczby niepotrzebnych arkuszy, które później musimy ręcznie usuwać. Aby pominąć tę czynność i zaoszczędzić czas dziś przygotujemy makro, które będzie miało na celu automatyczne usuwanie niepotrzebnych arkuszy. 

Określenie, które arkusze są niepotrzebne

Pierwsze nad czym musimy się zastanowić to określenie, czym charakteryzują się niepotrzebne arkusze. Nasze makro musi przecież zawierać warunki według których należy automatycznie usunąć niepotrzebne arkusze. Na szczęście jest to bardzo proste bo każdy nowy arkusz w Excelu ma standardową nazwę jako połączenie słowa Arkusz oraz kolejnego numeru, czyli Arkusz1, Arkusz2 itd. I w zasadzie to tyle, pod warunkiem że stosujemy odpowiednie nazewnictwo w naszym skoroszycie. Powinniśmy wyrobić sobie nawyk poprawnego nazywania arkuszy, które mają dla nas znaczenie. Najlepiej jeśli będą to dla nas powtarzalne nazwy np. Tabela, Dane, Słowniki itp. Przy tworzeniu odwołań w formułach, pisania makr czy innych działaniach będzie to spore ułatwienie. Błędem jest pozostawianie standardowych nazw arkuszy typu Arkusz1, Arkusz2, bo przy większym pliku skutecznie utrudni to nawigację po skoroszycie.

Oczywiście w samym makrze możemy warunek sprawdzania nazwy arkusza rozbudować, można sprawdzić wielkość liter albo czy po słowie arkusz znajduje się liczba. Jeżeli używamy nazewnictwa, które mogłoby wprowadzać makro w błąd np. Arkusz2020, Arkusz2021 to warto od razu umieścić to w kodzie VBA, aby takie arkusze pominąć. Usunięcia arkusza przecież nie cofniemy.

Usuwanie niepotrzebnych arkuszy – założenia dla makra

Zanim zaczniemy pisać makro warto zastanowić się nad jego założeniami tak, aby potem nie zmieniać tego co już napisaliśmy. Nasze założenia będą takie:

• używamy tylko zadeklarowanych zmiennych – czyli podajemy najpierw zmienne oraz ich typy jakie będziemy stosować. Takie założenie warto mieć przy każdym makrze, bo pozwala uniknąć literówek, które popsują makro
• usuwamy arkusze których nazwa zaczyna się od słowa „Arkusz” a po tym słowie jest jakaś cyfra. Dodatkowo aby uniknąć błędów dodamy warunek, aby nazwa arkusza była dłuższa niż 6 znaków
• makro ma przejść przez cały skoroszyt i usunąć wszystkie arkusze spełniające warunek
• podczas usuwania nie mają wyskakiwać żadne okienka, które zatrzymują działanie makra.

Usuwanie niepotrzebnych arkuszy – piszemy makro

Ok, znamy założenia, więc możemy zacząć pisać. Zaczynamy zgodnie ze sztuką od zadeklarowania zmiennych. Wystarczą nam tylko dwie. Pierwsza „NrArk” będzie wykorzystywana w pętli do określania numeru arkusza, po to aby przejść przez wszystkie arkusze. Jest to liczba, więc może to być zmienna „long”. Druga zmienna „NazwaArk” jest tylko po to, aby uprościć kod i nie powtarzać kilka razy tych samych poleceń. Zmienna będzie przechowywała nazwę arkusza, czyli tekst więc jest to typ „string”.

Usuwanie niepotrzebnych arkuszy ma działać bez przerywania wyskakującymi okienkami, dlatego użyjemy polecenia, które wyłącza wszystkie takie okienka Application.DisplayAlerts = False. Na samym końcu makra chcemy przywrócić normalne działanie Excela, dlatego wprowadzamy to samo polecenie z parametrem True.

Pętla

Przechodzimy do pętli. Zastosujemy najpopularniejszą i najprostszą pętlę For… Next. W większości przypadków chcemy, żeby makro przechodziło przez pierwszy arkusz, potem drugi, trzeci itd. Tu jednak jest pewien problem. Jeżeli mamy np. 3 arkusze i makro usunie drugi arkusz, a pętla będzie miała przejść do trzeciego arkusza to nie da rady, ponieważ skoroszyt zawiera już tylko dwa arkusze i wyrzuci błąd. W tym przypadku w pętli musiałoby działać dodatkowe zmienianie wartości licznika oraz wyliczanie liczby arkuszy w skoroszycie.

Na szczęście jest na to proste rozwiązanie. Zaczniemy analizować arkusze od końca, w ten sposób usunięcie arkusza nie wpływa na numery arkuszy które pozostały do przeaanalizowania w pliku. Użyjemy takiego zapisu pętli „For NrArk = Sheets.Count To 1 Step -1 …. Next”. To oznacza „zacznij od licznika równego licznie arkuszy w skoroszycie, skończ na wartości licznika 1, a wartość licznika w każdym kroku zmniejszaj o 1. W ten sposób przesuwamy się od końca pliku na sam początek.

Warunki

Następnie określamy warunki dla nazwy arkusza. Wykorzystujemy zmienną NazwaArk aby przypisać nazwę arkusza, który w danym momencie jest sprawdzany czyli „Sheets(NrArk).Name”. NrArk to nasz licznik z pętli. W pierwszym warunku sprawdzamy czy długość nazwy arkusza jest większa niż 6 znaków (jeśli nie to na pewno jest to niestandardowa nazwa arkusza), czyli sprawdzenie „Len(NazwaArk)>6”.

Drugi warunek to czy pierwsze 6 znaków równa się tekstowi „Arkusz”, czyli Left(NazwaArk,6)=”Arkusz” oraz czy 7 znak jest liczbą tj. IsNumeric(Mid(NazwaArk,7,1))=True.

Jeśli powyższe warunki są spełnione to usuwamy niepotrzebny arkusz poleceniem Sheets(NrArk).Delete.

Kod makra na usuwanie niepotrzebnych arkuszy

Poniżej zamieszczam gotowy kod na usuwanie niepotrzebnych arkuszy w pliku Excel. Pewnie zdziwiło Cię jaki jest krótki i prosty ?. Możesz też pobrać kod w pliku tekstowym wraz z opisami poszczególnych kroków. Niestety gotowego modułu do zaczytania w edytorze VBA nie można zamieszczać na serwerze z hostingiem strony…

Dim NrArk As Long
Dim NazwaArk As String

Sub UsunArkusze()
    
    Application.DisplayAlerts = False
    
    For NrArk = Sheets.Count To 1 Step -1
        NazwaArk = Sheets(NrArk).Name
        If Len(NazwaArk) > 6 Then
            If Left(NazwaArk, 6) = "Arkusz" And IsNumeric(Mid(NazwaArk, 7, 1)) = True Then
                Sheets(NrArk).Delete
            End If
        End If
    Next
        
    Application.DisplayAlerts = True
    
End Sub

Jak efektywnie korzystać z makra?

Aby korzystanie z makra było najwygodniejsze warto zrobić dwie czynności. Po pierwsze przypisać do niego wygodny i łatwy do zapamiętania skrót klawiaturowy, np. „Cntr+Shift+D” (D od delete). Druga rzecz to dodanie makra do arkusza makr osobistych, żeby móc z niego korzystać podczas pracy z dowolnym skoroszytem. Dokładny opis jak to zrobić oraz jak korzystać z tego skoroszytu znajdziesz pod tym linkiem LINK.

Makro na usuwanie niepotrzebnych arkuszy można też wykorzystać przy innych działaniach, np. w pliku w którym często działamy na tabelach przestawnych dodać uruchomienie tego makra w momencie zamykania i zapisywania pliku, po to żeby automatycznie pozbyć się niepotrzebnych arkuszy.

Jak widzisz pisanie makr, które zwiększają Twoją produktywność jest prostsze niż się wydaje. Dzięki niemu na pewno zaoszczędzisz czas i podniesiesz swój komfort pracy.