Makro til indsættelse af regneark - Excel-tip

"Stuck in NJ" stillede denne uges spørgsmål:

Jeg opretter en Excel-projektmappe, der indeholder diagrammer over salgstal. Det første Excel-regneark svarer til det første salgsområde i virksomheden (f.eks. FL01.) Regnearkets fanenavn (såvel som celle A1) er salgsområdets nummer FL01. Dataene, der driver diagrammerne, er til højre (uden for det område, jeg kan indstille til at udskrive) og udfyldes fra en anden excel-projektmappe ved hjælp af VLOOKUP med salgsområdet nummer FL01 i celle A1 som opslagstast. Jeg er nødt til at tilføje til projektmappen 76 identiske regneark (et for hvert salgsområde), således at hvert ark fanenavn og celle A1 svarer til successive salgsområdetum (f.eks. FL01, FL02, LK01, LK02.) Hvordan gør jeg det i en automatiseret måde?

Dette opnås let med en VBA-makro, men lad mig dække tipet til at gøre dette manuelt. Når du har oprettet et ark med formatering og udskriftsområder, kan du let duplikere arket ved at højreklikke på fanen for det ark. Vælg Flyt eller Kopi i den menu, der vises. I dialogboksen Flyt eller kopi skal du vælge "(flyt til slut)" og markere afkrydsningsfeltet "Opret en kopi", og derefter klikke på OK. En kopi af dit ark med formatering tilføjes som et nyt regneark i din projektmappe. (For at fremskynde denne proces skal du holde Ctrl-tasten nede, mens du trækker regnearkfanen til højre.) Du vil normalt omdøbe arket. Højreklik på den nye fane, vælg omdøb, og skriv et meningsfuldt navn.

For at Excel-makroen skal fungere, skal den have en liste over territorier. Inden du skriver makroen, skal du indsætte et nyt ark i denne bog med navnet Data. I celle A1 skal du indtaste det 2. salgsområde (FL02 i dit eksempel). Fortsæt med at indtaste alle salgsområder i kolonne A. Efterlad ikke tomme rækker.

Indsæt en ny makro, og kopier denne kode:

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

Makroen bruger kommandoen Ark (). Kopi, der gør det samme som at klikke på Flyt eller Kopi manuelt. Før jeg laver hver kopi, finder jeg ud af antallet af ark ved hjælp af egenskaben Sheets.Count. Jeg ved, at hvis der er 56 ark, og jeg indsætter et nyt ark, refereres det til Ark (57). Jeg finder det noget uforudsigeligt at gætte, hvordan Excel vil navngive det nye ark, så jeg bruger numeriske indeksnumre til at identificere arket. Når jeg først omdøber det nye ark med "Sheets (LastSheet + 1) .Name = ThisTerr", kan jeg derefter skifte tilbage til min foretrukne metode til at henvise til arket ved hjælp af dets navn.

Bemærk, at listen over territorier ikke bør indeholde FL01 på listen. Forsøg på at omdøbe en kopi en kopi af FL01 med samme navn vil forårsage en fejl i makroen.