How To Use VBA To Create Multiple Worksheets From A List In Excel

0
65

Sometimes it can be necessary to add and delete a number of worksheets – or tabs – in an Excel work book. Using a VBA procedure to perform this task is challenging, because you need to make sure you’re adding or deleting the correct worksheet.

This article will show you how to add and delete a number of tabs based on a data list in a separate sheet.

Adding The New Tabs

First, we’ll assume the following list of unique names exist in a separate worksheet and you want to add a new tab for each name. The list might be a grouping of employees or other activities that you want to keep a record of.


Names
John
Maria
Henri
Peter
Jacques
Mary

We’ll write some simple VBA code which will loop through the list and add a new sheet for each name. First, we’ll select the list.


Set d = ActiveCell.CurrentRegion

Next, we’ll loop through the list, ignoring the header row. We want to add each new worksheet after the last sheet so we use the worksheets.count property to place the new tab correctly and then rename it.


For x = 2 To d.Rows.Count
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = d(x)
Next

This is the type of procedure that might work well with a limited number of new worksheets. It’s best to avoid having a work book with too many worksheets and there are an excessive number of new sheets it might be necessary to look at the design of your data.

To delete the added sheets, you can reverse the process but you need to assign the sheet name to a string variable, instead of referencing the name from the current region collection.

One tip when deleting worksheets is to always reference the sheet by name, rather than index number. That way it reduces the chances of deleting the wrong sheet!


application.displayalerts=false
dim mySheet as string
For x = 2 To d.Rows.Count
mySheet=d(x)
Worksheets(mySheet).delete
Next

Code Enhancements

Some issues to consider include sorting the list alphabetically and ignoring any duplicates. One way to use only unique values is to keep a record of the added names:


myNames=""
For x = 2 To d.Rows.Count
if instr(myNames,d(x))=0 then
' take action
end if
myNames=myNames & "|"
Next

Summary

Adding and deleting tabs is a simple but important technique in any VBA developers toolbox. Deletion of any kind using VBA must be used carefully because of the lack of any “undo” commands and this code can be easily added to your code library for future reference.

Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here