Macro om werkblad in te voegen - Excel-tips

"Stuck in NJ" stelde de vraag van deze week:

Ik ben een Excel-werkmap aan het maken die grafieken met verkoopcijfers bevat. Het eerste Excel-werkblad komt overeen met het eerste verkoopgebied in het bedrijf (bijv. FL01.) De tabbladnaam van het werkblad (evenals cel A1) is het verkoopgebiednummer FL01. De gegevens die de grafieken aansturen, bevinden zich aan de rechterkant (buiten het afdrukbare bereik dat ik heb ingesteld) en worden ingevuld vanuit een andere Excel-werkmap met behulp van VERT.ZOEKEN met het verkoopregionummer FL01 in cel A1 als de opzoeksleutel. Ik moet 76 identieke werkbladen aan de werkmap toevoegen (één voor elk verkoopgebied) zodat de tabnaam van elk blad en cel A1 gelijk zijn aan opeenvolgende verkoopgebiednummers (bijv.FL01, FL02, LK01, LK02.) Hoe doe ik dat in een geautomatiseerde manier?

Dit is gemakkelijk te bereiken met een VBA-macro, maar laat me de tip behandelen om dit handmatig te doen. Als u eenmaal een blad heeft ingesteld met opmaak- en afdrukbereiken, kunt u het blad gemakkelijk dupliceren door met de rechtermuisknop op het tabblad voor dat blad te klikken. Kies Verplaatsen of Kopiëren in het menu dat verschijnt. Selecteer in het dialoogvenster Verplaatsen of Kopiëren "(verplaats naar einde)" en vink het vakje "Een kopie maken" aan en klik vervolgens op OK. Een kopie van uw blad met opmaak wordt als nieuw werkblad aan uw werkmap toegevoegd. (Om dit proces te versnellen, houdt u de Ctrl-toets ingedrukt terwijl u de werkbladtab naar rechts sleept.) Meestal wilt u de naam van het blad wijzigen. Klik met de rechtermuisknop op het nieuwe tabblad, kies hernoemen en typ een betekenisvolle naam.

Om de Excel-macro te laten werken, heeft deze een lijst met territoria nodig. Voordat u de macro schrijft, voegt u een nieuw blad in dit boek in met de naam Gegevens. Voer in Cel A1 het 2e verkoopgebied in (FL02 in uw voorbeeld). Ga door met het invoeren van alle verkoopregio's in kolom A. Laat geen lege rijen achter.

Voeg een nieuwe macro in en kopieer deze code:

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

De macro maakt gebruik van de opdracht Sheets (). Copy, die hetzelfde doet als handmatig klikken op Verplaatsen of Kopiëren. Voordat ik elke kopie maak, zoek ik het aantal vellen op met behulp van de eigenschap Sheets.Count. Ik weet dat als er 56 vellen zijn en ik een nieuw vel invoegen, er naar wordt verwezen als Vellen (57). Ik vind het enigszins onvoorspelbaar om te raden hoe Excel het nieuwe blad een naam zal geven, dus ik gebruik numerieke indexnummers om het blad te identificeren. Zodra ik het nieuwe blad hernoem met "Sheets (LastSheet + 1) .Name = ThisTerr", kan ik terugschakelen naar mijn voorkeursmethode om naar het blad te verwijzen door de naam te gebruiken.

Merk op dat de lijst met territoria FL01 niet in de lijst mag bevatten. Als u een kopie van een kopie van FL01 met dezelfde naam probeert te hernoemen, zal er een fout in de macro optreden.