excel macro to create sheets

Microsoft Microsoft excel 2007 open lice...
April 26, 2010 at 16:44:43
Specs: Windows Xp
I need to create a macro that creates a set of multiple
sheets based
upon a number entered. For example: I enter the
number 20 and then
run the macro sheets A1,B1,C1,D1,E1,&F1 are
created and repeated 20
times with the second set being
A2,B2,C2,D2,E2,&F2, etc, all the way
to A20,B20....... My knowledge in this area is pretty
limited so any
help is greatly appreciated.

See More: excel macro to create sheets

Report •


#1
April 26, 2010 at 16:49:58
re: "sheets A1,B1,C1,D1,E1,&F1 are created and repeated 20 times with the second set being A2,B2,C2,D2,E2,&F2, etc"

Are you saying that the sheet names should be A1, B1, C1, etc.?

So, if you enter the number 20, the code should create 180 sheets named A1 though F20?


Report •

#2
April 26, 2010 at 18:41:21
There would be 20 sets of the 6 sheets (A through F) resulting in 120 sheets total. That is assuming you input the number 20. If you were to enter the number 2 instead of 20 the output would be two sets of the 6 sheets (A1, B1, C1, D1, E1, F1 & A2, B2, C2, D2, E2, F2)

Report •

#3
April 27, 2010 at 06:27:05
Hi,

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.
Select AddWSsets.xlam
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:

Option Explicit

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"
    Exit Sub
End If

'get number of new worksheets
Num:
strWSnum = InputBox("Enter number of new Worksheets required", "New Worksheet")
If Not IsNumeric(strWSnum) Then
    MsgBox "enter a valid number"
    GoTo Num
End If

'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
        Next
        If blnSame = False Then
            'create sheet and name it only if no match to existing name
            Workbooks(strWBname).Sheets.Add After:=Worksheets(Worksheets.Count)
            Workbooks(strWBname).Worksheets(Worksheets.Count).Name = strWSname
            Else
            'name matches existing name
            MsgBox "This name " & strWSname & " already exists - the program will quit"
            Exit Sub
        End If
    Next m
Next n
End Sub

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>>'
Click OK.
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.

Regards


Report •

Related Solutions

#4
April 27, 2010 at 08:23:27
That worked great! If I wanted specific names to be repeated in sets instead of the sequential A-F, how would I go about doing that? For example: Game1, Score1,........ Game2, Score2,...... Up to the number of sets needed.

Thanks for all the help.


Report •

#5
April 27, 2010 at 08:45:07
Actually.....(Team A, Game A1, Game A2, Game A3, Game A4, Results A) would be the best format, with the second set of tabs being ( Team B, Game B1, Game B2, Game B3, Game B4, Results B). I think the alphabetic sequence would work best. After set Z would be sets AA through ZZ then AAA through ZZZ etc.

Report •

#6
April 28, 2010 at 04:51:03
Hi,

It is possible to add worksheets in groups from A to ZZZ

This makes a possible total of 91,390 worksheets:
A-Z = 26
AA-ZZ = 676
AAA-ZZZ = 17576
i.e. 18,278
with 5 worksheets in each set (4 games and 1 result) = 91,390

I wonder if this is the best approach.
If you want to do any analysis such as totals, averages, maximums or minimums, the formulas are going to be very complicated, working across potentially thousands of worksheets.

It is possible to use 3D formulas which work across worksheets, but it gets hard to keep track of what is going on. Tracking down an error or an inconsistency will become increasingly difficult as more worksheets are added.

It might be better to consider using one worksheet to hold game results for all teams, or even four worksheets one for game 1 results for all teams, one for game 2 results for all teams etc.

You can then have a Results worksheet which holds the results based on all 4 games for every team.

Finally you can have an Analysis page that uses the results page and holds summaries and analyses.

It would be possible to write a macro to add the headings for new teams on the game and result worksheets, including formatting, totals formulas etc.

Regards


Report •

#7
April 28, 2010 at 05:34:58
I'm thinking more along the lines of (Team A, Game A1, Game A2, Game A3, Game A4, Results A) being my set of 6 sheets. When I enter the number 2 as before it creates two sets of sheets being (Team A, Game A1, Game A2, Game A3, Game A4, Results A) and (Team B, Game B1, Game B2, Game B3, Game B4, Results B). In essence it is counting using the alphabet instead of numbers. A=1, AA=27, AAA=53. So when I type in the number 53 the 53rd set would be (Team AAA, Game AAA1, Game AAA2, Game AAA3, Game AAA4, Results AAA) resulting in a total of 318 tabs. As of right now all I really need is for the sheets to be generated. I hope this helps clarify things a bit. Again, thanks for all of the help.

Report •

#8
April 28, 2010 at 06:40:59
Hi,

This routine creates groups of 6 sheets:
One Team, Four Games, One Result

The letters used are from A to Z, then AA to ZZ and then AAA on
Note that team 53 is BA not AAA

As 'counting' in letters beyond 26 is difficult - Z+1 has to equal AA, this routine uses Excel's column letters as a means of counting. If this is used in Excel 2003 the maximum will be IV (256).
The maximum in Excel 2007 is XFD (16,384)

The macro finds the last letter already used and starts adding new worksheets with the next letter. If there is a sheet named "Team ZZ", the next group will start with "Team AAA", followed by Game AAA1 etc.

I still think that you should consider changing the approach from using worksheets as the 'index' to using blocks of rows in a small number of worksheets - it will make the longer-term use of the data much easier and it will make managing the data easier - tracking down an error when you have 1000 worksheets is much harder than finding an error in three or four albeit large, worksheets.

Anyway here is the revised code. I have only tested it in Excel 2003 as I don't have Excel 2007 on this PC.

Option Explicit

Public Sub MakeWorksheets()

Dim strResp As String
Dim ws As Worksheet
Dim rngCol As Range
Dim strLastLtr As String
Dim strThisLtr As String
Dim strNextLtr As String
Dim strWBName As String
Dim strWSnum As String
Dim strWSName As String
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"
    Exit Sub
End If

'get number of new worksheets
Num:
strWSnum = InputBox("Enter number of new Worksheets required", "New Worksheet")
If Not IsNumeric(strWSnum) Then
    MsgBox "Enter a valid number" & vbCrLf & "Please try again"
    GoTo Num
End If

'find last worksheet letter(s), based on Team sheet team letter
'set initial 'letter' to the character before "A"
strLastLtr = Chr(63)
For Each ws In Workbooks(strWBName).Worksheets()
    'look at worksheet names containing 'Result'
    If InStr(1, ws.Name, "Team") <> 0 Then
        'extract the team letter
        strThisLtr = Mid(ws.Name, 6, Len(ws.Name) - 5)
        'compare team letter(s) to existing 'highest' letter found
        If strThisLtr > strLastLtr Or Len(strThisLtr) > Len(strLastLtr) Then
            'if this letter is 'higher' - then keep it
            strLastLtr = strThisLtr
        End If
    End If
Next

'letters are incremented using the Excel column letters
'this means that we don't have to do lots of calculations
'to move from AZ to BA for example - Excel columns do it for us
'In Excel 2003 we can only go from A to IV, but lots more in Excel 2007

'set letter to start this series with
If strLastLtr = Chr(63) Then
    'No team letter found, so start with column A address
    Set rngCol = Range("A1")
    Else
    'team letter(s) found, so use next column address
    Set rngCol = Range(strLastLtr & "1").Offset(0, 1)
End If

'create a series of sets of worksheets
For n = 1 To CInt(strWSnum)
    'get next letter from the column Address (e.g., $BB$1 is team BB)
    strNextLtr = Mid(rngCol.Offset(0, n - 1).Address, 2, InStr(2, _
                        rngCol.Offset(0, n - 1).Address, "$") - 2)
        'team
        strWSName = "Team " & strNextLtr
        If TestName(strWBName, strWSName) = False Then
            'create sheet and name it only if no match to existing name
            Workbooks(strWBName).Sheets.Add After:=Worksheets(Worksheets.Count)
            Workbooks(strWBName).Worksheets(Worksheets.Count).Name = strWSName
            Else
            'name matches existing name
            MsgBox "This name " & strWSName & " already exists - " _
                    & " the program will quit"
            Exit Sub
        End If
        'four games
        For m = 1 To 4
            strWSName = "Game " & strNextLtr & CStr(m)
            'the TestName is a function returning true or false
            If TestName(strWBName, strWSName) = False Then
                'create sheet and name only if no match to existing name
                Workbooks(strWBName).Sheets.Add After:=Worksheets(Worksheets.Count)
                Workbooks(strWBName).Worksheets(Worksheets.Count).Name = strWSName
                Else
                'name matches existing name
                MsgBox "This name " & strWSName & " already exists - " _
                        & " the program will quit"
                Exit Sub
            End If
        Next m
        'result
        strWSName = "Result " & strNextLtr
        If TestName(strWBName, strWSName) = False Then
            'create sheet and name it only if no match to existing name
            Workbooks(strWBName).Sheets.Add After:=Worksheets(Worksheets.Count)
            Workbooks(strWBName).Worksheets(Worksheets.Count).Name = strWSName
            Else
            'name matches existing name
            MsgBox "This name " & strWSName & " already exists - " _
                    & " the program will quit"
            Exit Sub
        End If
Next n
End Sub

Function TestName(strWBName As String, strWSName As String) As Boolean
Dim blnSame As Boolean
Dim ws As Worksheet
'test for existing name
blnSame = False
For Each ws In Workbooks(strWBName).Worksheets()
    If ws.Name = strWSName Then blnSame = True
Next
'return true if name exists in selected workbook
TestName = blnSame
End Function

Regards


Report •

#9
April 28, 2010 at 08:13:58
That works perfectly. I think I'm all set now. Thanks again for your help.

Report •

#10
April 28, 2010 at 08:27:17
Thanks for the feedback.

Good luck with it

Regards

Humar


Report •


Ask Question