„Stuck in NJ” zadał pytanie z tego tygodnia:
Tworzę skoroszyt programu Excel zawierający wykresy wyników sprzedaży. Pierwszy arkusz programu Excel odpowiada pierwszemu obszarowi sprzedaży w firmie (np. FL01). Nazwa zakładki arkusza (a także komórka A1) to numer obszaru sprzedaży FL01. Dane sterujące wykresami znajdują się po prawej stronie (poza ustawionym przeze mnie zakresem drukowania) i są zapełniane z innego skoroszytu programu Excel przy użyciu funkcji WYSZUKAJ.PIONOWO z numerem obszaru sprzedaży FL01 w komórce A1 jako kluczem wyszukiwania. Muszę dodać do skoroszytu 76 identycznych arkuszy (po jednym dla każdego obszaru sprzedaży), tak aby nazwa karty i komórka A1 każdego arkusza odpowiadały kolejnym numerom obszarów sprzedaży (np. FL01, FL02, LK01, LK02). Jak to zrobić w zautomatyzowany sposób?
Można to łatwo osiągnąć za pomocą makra VBA, ale pozwólcie, że omówię wskazówkę dotyczącą robienia tego ręcznie. Po skonfigurowaniu arkusza z formatowaniem i zakresami drukowania możesz łatwo zduplikować arkusz, klikając prawym przyciskiem myszy kartę tego arkusza. Z menu, które się pojawi, wybierz Przenieś lub Kopiuj. W oknie dialogowym Przenieś lub Kopiuj wybierz „(przesuń na koniec)” i zaznacz pole „Utwórz kopię”, a następnie kliknij OK. Kopia arkusza z formatowaniem zostanie dodana jako nowy arkusz w skoroszycie. (Aby przyspieszyć ten proces, przytrzymaj klawisz Ctrl podczas przeciągania karty arkusza w prawo). Zwykle będziesz chciał zmienić nazwę arkusza. Kliknij prawym przyciskiem myszy nową kartę, wybierz opcję zmień nazwę i wpisz opisową nazwę.
Aby makro Excel działało, potrzebna jest lista terytoriów. Zanim napiszesz makro, wstaw do tej książki nowy arkusz o nazwie Dane. W komórce A1 wprowadź drugi obszar sprzedaży (w przykładzie FL02). Kontynuuj wprowadzanie wszystkich obszarów sprzedaży w kolumnie A. Nie zostawiaj pustych wierszy.
Wstaw nowe makro i skopiuj ten kod:
Public Sub CopyIt() Sheets("Data").Select ' Determine how many territories are on Data sheet FinalRow = Range("A65000").End(xlUp).Row ' Loop through each territory on the data sheet For x = 1 To FinalRow LastSheet = Sheets.Count Sheets("Data").Select ThisTerr = Range("A" & x).Value ' Make a copy of Fl01 and move to end Sheets("FL01").Copy After:=Sheets(LastSheet) ' rename the sheet and set A1 = to the territory name Sheets(LastSheet + 1).Name = ThisTerr Sheets(ThisTerr).Select Range("A1").Value = ThisTerr Next x End SubMakro korzysta z polecenia kopiowania Arkusze ()., Które robi to samo, co ręczne kliknięcie Przenieś lub Kopiuj. Przed wykonaniem każdej kopii sprawdzam liczbę arkuszy za pomocą właściwości Sheets.Count. Wiem, że jeśli jest 56 arkuszy i wstawię nowy arkusz, będzie on oznaczony jako Arkusze (57). Wydaje mi się nieco nieprzewidywalne, aby zgadnąć, jak Excel nada nazwę nowemu arkuszowi, więc używam numerycznych numerów indeksowych do identyfikacji arkusza. Po zmianie nazwy nowego arkusza na „Arkusze (LastSheet + 1) .Name = ThisTerr”, mogę wrócić do preferowanej metody odwoływania się do arkusza, używając jego nazwy.
Pamiętaj, że lista terytoriów nie powinna zawierać FL01 na liście. Próba zmiany nazwy kopii kopii FL01 o tej samej nazwie spowoduje błąd w makrze.
