Makro pro vložení listu - tipy pro Excel

„Stuck in NJ“ položil otázku tohoto týdne:

Vytvářím sešit aplikace Excel, který obsahuje grafy prodejních čísel. První list aplikace Excel odpovídá prvnímu prodejnímu území ve společnosti (např. FL01.) Název karty listu (stejně jako buňka A1) je číslo prodejního území FL01. Data, která řídí grafy, jsou napravo (mimo rozsah tisku, který jsem nastavil) a jsou naplněna z jiného sešitu aplikace Excel pomocí VLOOKUP s prodejním územním číslem FL01 v buňce A1 jako vyhledávacím klíčem. Musím do sešitu přidat 76 stejných pracovních listů (jeden pro každé prodejní území) tak, aby se název záložky a buňka A1 každého listu rovnal postupným číslům prodejního území (např. FL01, FL02, LK01, LK02.) Jak to provedu v automatizovaným způsobem?

Toho lze snadno dosáhnout pomocí makra VBA, ale dovolte mi pokrýt tip, jak to udělat ručně. Jakmile máte nastavený list s rozsahy formátování a tisku, můžete list snadno duplikovat kliknutím pravým tlačítkem na kartu daného listu. Z rozbalovací nabídky vyberte Přesunout nebo Kopírovat. V dialogovém okně Přesunout nebo Kopírovat vyberte „(přesunout na konec)“, zaškrtněte políčko „Vytvořit kopii“ a klikněte na OK. Kopie listu s formátováním bude přidána jako nový list do sešitu. (Chcete-li tento proces urychlit, podržte při přetahování karty listu doprava klávesu Ctrl.) List obvykle budete chtít přejmenovat. Klepněte pravým tlačítkem na novou kartu, zvolte přejmenovat a zadejte smysluplný název.

Aby makro aplikace Excel fungovalo, potřebuje seznam teritorií. Než začnete psát makro, vložte do této knihy nový list s názvem Data. V buňce A1 zadejte druhé prodejní území (ve vašem příkladu FL02). Pokračujte v zadávání všech prodejních teritorií dolů do sloupce A. Nenechávejte prázdné řádky.

Vložte nové makro a zkopírujte tento kód:

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 Sub

Makro využívá příkaz Sheets (). Copy, který dělá totéž jako kliknutí na Přesunout nebo Kopírovat ručně. Před provedením každé kopie zjistím počet listů pomocí vlastnosti Sheets.Count. Vím, že pokud existuje 56 listů a vložím nový list, bude na něj odkazovat jako na listy (57). Připadá mi poněkud nepředvídatelné hádat, jak Excel pojmenuje nový list, takže k identifikaci listu používám číselná čísla indexu. Jakmile nový list přejmenuji na „Listy (LastSheet + 1) .Name = ThisTerr“, mohu se poté přepnout zpět na svoji preferovanou metodu odkazování na list pomocí jeho názvu.

Upozorňujeme, že seznam teritorií by v seznamu neměl obsahovat FL01. Pokus o přejmenování kopie na kopii FL01 se stejným názvem způsobí chybu v makru.