This macro will create the required number of sets of new worksheets in the Active workbook.
As the new worksheets will be created in different workbooks, the macro must be stored in a location that is always available to Excel. I suggest using an Add-In specifically for this purpose (.xlam)
Once created the Add-In can be made to load automatically whenever Excel starts.
The Add-In can also be copied to other users as required.
The macro displays the name of the active workbook that will have the new worksheets added, and asks for confirmation.
Then the user is asked for the number of sets required.
If a number is entered, that number of sets is created.
The macro will be run from a toolbar button.
Create a new Add-In (*.xlam) as follows:
Open a blank workbook
'Save As' and choose 'Other Formats'
From the Dialog box that opens select Excel Add-In (*.xlam) (Its well down the list)
Enter a name - e.g., AddWSsets.xlam
Save in a suitable location that will always be available to Excel. Using the default Add-In folder which Excel suggests should be OK.
Now make the Add-In active, and set it to automatically load, as follows:
Click the Office button and select 'Excel options' (at the bottom of the Office dialog box)
Select the 'Add-Ins' tab on the left
In the 'Manage:' section at the bottom of the Add-Ins window select 'Excel Add-Ins' and click 'Go...'
In the Add-Ins dialog box select 'Browse...' and select your new xlam file (the browse selector opens to the default Add-In location), else navigate to where you saved the xlam file.
Click OK, and the Add-In will be added to the list in the dialog box.
Make sure that its box is checked, then click OK
In Excel, click Alt+f11 (the Alt key and function key 11 pressed together)
In the Visual Basic window that opens, look for 'VBAProject(AddWSsets.xlam)' in the Project Explorer pane.
If the Project Explorer pane is not visible, from the VB menu select 'View' and 'Project Explorer'
Right-click on VBAProject(AddWSsets.xlam) and select 'Insert' and 'Module' (not Class module)
Find the new module (typically Module1) in the 'Modules' part of the tree, below VBAProject(AddWSsets.xlam)
Double click the new module's name.
In the large VB window on the right, enter this:
Public Sub MakeWorksheets()
Dim strResp As String
Dim strWBname As String
Dim strWSnum As String
Dim strWSname As String
Dim ws As Worksheet
Dim blnSame As Boolean
Dim m As Integer
Dim n As Integer
'get the active workbook's name
strWBname = ActiveWorkbook.Name
'check that this is the one for the new worksheets
strResp = MsgBox("The new worksheets will be created in the " & strWBname & _
" workbook" & vbCrLf & "Click OK to continue or Cancel", vbOKCancel)
If strResp <> vbOK Then
MsgBox "Make the required workbook the active workbook before running this routine"
'get number of new worksheets
strWSnum = InputBox("Enter number of new Worksheets required", "New Worksheet")
If Not IsNumeric(strWSnum) Then
MsgBox "enter a valid number"
'create a series of worksheets
For n = 1 To CInt(strWSnum)
For m = 1 To 6
'create worksheet name - letter + number
strWSname = Chr(m + 64) & CStr(n)
'test for existing name
blnSame = False
For Each ws In Workbooks(strWBname).Worksheets()
If ws.Name = strWSname Then blnSame = True
If blnSame = False Then
'create sheet and name it only if no match to existing name
Workbooks(strWBname).Worksheets(Worksheets.Count).Name = strWSname
'name matches existing name
MsgBox "This name " & strWSname & " already exists - the program will quit"
From the VB menu click 'File' then 'Save AddWSsets.xlam
Then 'Close and Return to Microsoft Excel'
Now create a button on the toolbar as follows:
Right-click on the 'Quick Access' toolbar and select 'Customize Quick Access Toolbar'
In the dialog box that opens select the 'Customize' tab on the left
From the 'Choose commands from:' drop-down, select 'Macros'
Select 'MakeWorksheets' from the list on the left and click 'Add>>'
A macro button will now be available on the Quick Access toolbar.
(Excel 2007 does not include a simple means to edit the button image - it can be done - ask if you need to change the image e.g., if you have more than one macro button - both with the same icon image)
Now open a new workbook to test the macro.
The new workbook will be 'Book1' or similar.
Click the new button and you should be able to create new sets of worksheets in Book1.
I have only tested this macro on my system, so you will need to test it in your environment. If it does not work 'as expected' please provide as much information as possible about what did not work and how you were using it at the time.