Creating multiple sheets consecutively

[angelocyrl]

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]

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

 

[Akashwani]

Hi welcome to MrExcel,

I personally cannot help, but this might...

http://www.mrexcel.com/articles/macro-to-insert-worksheet.php

Ak

 

[angelocyrl]

I am using Excel 2007

 

[AnAnalyst]

Ok, paste the following code into a standard module in a new worksheet:

Sub CreateSheets()

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

 

[Legacy 141710]

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

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]

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:

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

 

[angelocyrl]

Yeah, the code works, I thank everyone of your efforts.

 

[angelocyrl]

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]

Hi there,

Try the following:

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