Get In Touch
606 Tower A Plot, I-Thum Tower, Sector 62, Noida, Uttar Pradesh, IN
Ph: +91 8383.033.985
Work Inquiries
Ph: +91 8383.033.985

How To Use VBA To Create A number of Worksheets From A Checklist In Excel


Typically it may be vital so as to add and delete a variety of worksheets – or tabs – in an Excel work ebook. Utilizing a VBA process to carry out this job is difficult, as a result of you want to be sure you're including or deleting the right worksheet.

This text will present you how you can add and delete a variety of tabs based mostly on an information record in a separate sheet.

Including The New Tabs

First, we'll assume the next record of distinctive names exist in a separate workbook and also you wish to add a brand new tab for every identify. The record is perhaps a grouping of workers or different actions that you just wish to hold a report of.


We'll write some easy VBA code which can loop via the record and add a brand new sheet for every identify. First, we'll choose the record.

Set d = ActiveCell.CurrentRegion

Subsequent, we'll loop via the record, ignoring the header row. We wish to add every new worksheet after the final sheet so we use the worksheets.rely property to put the brand new tab appropriately after which rename it.

For x = 2 To d.Rows.Depend
Worksheets.Add After: = Worksheets (Worksheets.Depend)
Worksheets (Worksheets.Depend) .Identify = d (x)

That is the kind of process which may work nicely with a restricted variety of new workouts. It's finest to keep away from having a piece ebook with too many worksheets and there are an additional variety of new sheets it is perhaps vital to have a look at the design of your knowledge.

To delete the added sheets, you may reverse the method however you want to assign the sheet identify to a string variable, as an alternative of referencing the identify from the present area assortment.

One tip when deleting worksheets is to at all times reference the sheet by identify, relatively than index quantity. That manner it reduces the probabilities of eliminating the fallacious sheet!

software.displayalerts = false
dim mySheet as string
For x = 2 To d.Rows.Depend
mySheet = d (x)
Worksheets (mySheet) .delete

Code Enhancements
Some points to think about embody sorting the record alphabetically and ignoring any duplicates. A technique to make use of solely distinctive values ​​is to maintain a report of the added names:

myNames = ""
For x = 2 To d.Rows.Depend
if instr (myNames, d (x)) = zero then
'take motion
finish if
myNames = myNames & "|"

Including and deleting tabs is a straightforward however essential approach in any VBA builders toolbox. Deletion of any sort utilizing VBA have to be used fastidiously due to the dearth of any “undo” instructions and this code can simply be added to your code library for future reference.

Supply by Andy L Gibson

Post a comment

Your email address will not be published. Required fields are marked *