Creating multiple sheets consecutively

angelocyrl

New Member
Joined
Feb 28, 2010
Messages
18
1. I want to create 365 sheets consecutively starting from 01JAN11 to 31DEC11

2. After creating 365 sheets, based on above sheet name format I want to insert date in a row in respective sheet as [$-409]dddd,mm,dd,yyyy (for ex. Saturday, January 01, 2011).

Can anyone guide?
 

AnAnalyst

Well-known Member
Joined
Jul 10, 2007
Messages
536
Hi there,

Which version of Excel are you using? It's very unlikely Excel will cope with 365 sheets!

What are you actually trying to achieve?

Is this really the best way to arrange your data?

All the best :)
 

AnAnalyst

Well-known Member
Joined
Jul 10, 2007
Messages
536
Ok, paste the following code into a standard module in a new worksheet:

Sub CreateSheets()

Code:
Dim varDate As Date, _
    shtWs As Worksheet, _
    wbkNewBook As Workbook, _
    intShtCount As Integer, _
    intShtsToAdd As Integer

varDate = "01/01/2010"
intShtsToAdd = 31

Set wbkNewBook = Workbooks.Add

    intShtCount = wbkNewBook.Sheets.Count
    
    wbkNewBook.Sheets.Add Count:=intShtsToAdd - intShtCount
    
    For Each shtWs In wbkNewBook.Worksheets
        With shtWs
            .Name = Format(varDate, "ddmmmyy")
            .Range("A1").Value = Format(varDate, "dddd, Mmmm dd, yyyy")
        End With
        varDate = varDate + 1
    Next shtWs

End Sub
The code creates a new workbook, adds the number of sheets specified by "intShtsToAdd", changes each sheet name to the format "ddmmmyy" and writes a date in the required format to cell A1 of every sheet.

You can change the number of sheets it creates by adjusting intShtsToAdd = 31, BUT the maximum I can get out of Excel 2007 is about 258 sheets!

All the best :)
 
L

Legacy 141710

Guest
hi, i had a go at this as well and came up with this.

Code:
Sub add_sheets()
    Dim i As Date
    Dim j As Date
    i = "01/01/2011"
    j = "31/12/2011"
    
    Do While j >= i
    Sheets.Add
    ActiveSheet.Select
    ActiveSheet.Name = Format(j, "dd mmm yyyy")
    Range("A1").Select
    ActiveCell.FormulaR1C1 = DateValue(j)
    Selection.NumberFormat = "dddd, mmmm dd, yyyy"
    Columns("A:A").EntireColumn.AutoFit
    j = j - 1
    Loop
End Sub
it creates the sheet in the workbood you are in... worked for me :)
 

AnAnalyst

Well-known Member
Joined
Jul 10, 2007
Messages
536
Hi again,

Well, go figure! It appears that even though Excel 2007 will cope with having more sheets in a workbook than previous versions, the "Sheets.Add" method has a limit of 255 sheets! One learns something new every day! :)

I don't usually work with this many sheets in a single workbook as it's rarely the best format for the kind of stuff I work with, but if it fits what you need to do and you don't require compatibility with previous versions, then lefty74's code will work for you, or you can try my amended code as below:

Code:
Sub AddSheets()
    Dim varDate As Date, _
        shtWs As Worksheet, _
        wbkNewBook As Workbook, _
        intShtCount As Integer, _
        intShtsToAdd As Integer
    varDate = "01/01/2011"
    intShtsToAdd = 365
    Set wbkNewBook = Workbooks.Add
        intShtCount = wbkNewBook.Sheets.Count
        intShtsToAdd = intShtsToAdd - intShtCount
        Do While intShtsToAdd > 0
            wbkNewBook.Sheets.Add After:=Sheets(Sheets.Count)
            intShtsToAdd = intShtsToAdd - 1
        Loop
        For Each shtWs In wbkNewBook.Sheets
            With shtWs
                .Name = Format(varDate, "ddmmmyy")
                .Range("A1").Value = Format(varDate, "dddd, Mmmm dd, yyyy")
            End With
            varDate = varDate + 1
        Next shtWs
    Set wbkNewBook = Nothing
End Sub
Hope this helps, all the best :)
 
Last edited:

angelocyrl

New Member
Joined
Feb 28, 2010
Messages
18
Ok freinds, thanks for your codes, now I want to know how to add tab colors for all the work sheets created. For example if first one is yellow then next one is red and again yellow then red.
 

AnAnalyst

Well-known Member
Joined
Jul 10, 2007
Messages
536
Hi there,

Try the following:

Code:
Sub AddSheets()
    Dim varDate As Date, _
        shtWs As Worksheet, _
        wbkNewBook As Workbook, _
        intShtCount As Integer, _
        intShtsToAdd As Integer
    varDate = "01/01/2011"
    intShtsToAdd = 365
    Set wbkNewBook = Workbooks.Add
        intShtCount = wbkNewBook.Sheets.Count
        intShtsToAdd = intShtsToAdd - intShtCount
        Do While intShtsToAdd > 0
            wbkNewBook.Sheets.Add After:=Sheets(Sheets.Count)
            intShtsToAdd = intShtsToAdd - 1
        Loop
        For Each shtWs In wbkNewBook.Sheets
[COLOR="red"]            If intGreen = 255 Then
                intGreen = 0
            Else
                intGreen = 255
            End If[/COLOR]
            With shtWs
                .Name = Format(varDate, "ddmmmyy")
                .Range("A1").Value = Format(varDate, "dddd, Mmmm dd, yyyy")
                [COLOR="Red"].Tab.Color = RGB(255, intGreen, 0)[/COLOR]
            End With
            varDate = varDate + 1
        Next shtWs
    Set wbkNewBook = Nothing
End Sub
All the best :)
 

Forum statistics

Threads
1,082,577
Messages
5,366,427
Members
400,890
Latest member
Danni_8oii

Some videos you may like

This Week's Hot Topics

Top