Creating multiple sheets consecutively

angelocyrl · Mar 26, 2010

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?

Legacy 141710 · Mar 28, 2010

and here is mine less advanced version

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")
    
    If color_tab = 255 Then
    color_tab = 65535
    Else
    color_tab = 255
    End If
    
    With ActiveSheet.Tab
        .Color = color_tab
        .TintAndShade = 0
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = DateValue(j)
    Selection.NumberFormat = "dddd, mmmm dd, yyyy"
    Columns("A:A").EntireColumn.AutoFit
    j = j - 1
    Loop
End Sub

angelocyrl · Mar 28, 2010

Ok, thankyou friends for all your replies. Now, I have another one.

1. I have created a worksheet named 01JAN11 with yellow tab colour.
2. In one row I have a date as Saturday, 01 January, 2011 based on worksheet name.
3. In that sheet I have some rows and columns with formulas as well as data which is protected.
4. Now I want to create copies of the same worksheet upto 31DEC11 with same formulas and datas.

For example 02JAN11 with green tab colour and changing the date to Sunday, 02 January, 2011 then 03JAN11 with yellow tab colour and changing the date to Monday, 03 January, 2011 and so on.

AnAnalyst · Mar 29, 2010

Okay, the following code should be pasted into a standard module. It creates the dated sheets in the same workbook as your original sheet. The original sheet could be called something like "Template" and the code will start from 01/01/2011, otherwise if the sheet "01Jan11" exists it will start adding sheets from 02/01/2011. I haven't added error checking to ensure that new sheet names are not the same as any existing sheet names in the workbook. If this is the case the module will fail to work.

Code:

Sub AddSheets()
    Dim varStartDate As Date, _
        varEndDate As Date, _
        varCurDate As Date, _
        shtWs As Worksheet, _
        wbkBk As Workbook, _
        intShtStart As Integer, _
        intShtCount As Integer, _
        intRed As Integer
    varStartDate = "01/01/2011"
    varEndDate = "31/12/2011"
    Set wbkBk = ThisWorkbook
    On Error Resume Next
    Set shtWs = wbkBk.Sheets(Format(varStartDate, "ddmmmyy"))
    On Error GoTo 0
        If Not shtWs Is Nothing Then
            varCurDate = varStartDate + 1
            intShtStart = shtWs.Index
            Set shtWs = Nothing
        Else
            varCurDate = varStartDate
            intShtStart = ActiveSheet.Index
        End If
    Do While varCurDate <= varEndDate
        With wbkBk
            .Sheets(intShtStart).Copy After:=Sheets(intShtStart)
            intShtStart = intShtStart + 1
            .Sheets(intShtStart).Activate
        End With
        If intRed = 255 Then
            intRed = 0
        Else
            intRed = 255
        End If
        With ActiveSheet
            .Name = Format(varCurDate, "ddmmmyy")
            .Range("A1").Value = Format(varCurDate, "dddd, Mmmm dd, yyyy")
            .Tab.Color = RGB(intRed, 255, 0)
        End With
        varCurDate = varCurDate + 1
    Loop
End Sub

Hope this helps, all the best

angelocyrl · Mar 29, 2010

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Thank you very much for the code (AnAnalyst). It works perfectly. Okay, I need another code that is to be included, I have a numeric data in cell J70 of 01JAN11 and this data is to be added in cell J70 of 02JAN11 with total in cell K69 of the current page. For this I use the formula =SUM('01JAN11'!K70+K69).

I want to insert this formula in all pages with previous sheet name upto 31DEC11.

AnAnalyst · Mar 29, 2010

Okay, try this:

Code:

Sub AddSheets()
    Dim varStartDate As Date, _
        varEndDate As Date, _
        varCurDate As Date, _
        shtWs As Worksheet, _
        wbkBk As Workbook, _
        intShtStart As Integer, _
        intShtCount As Integer, _
        intRed As Integer, _
        [COLOR="red"]strFormula as String[/COLOR]
    varStartDate = "01/01/2011"
    varEndDate = "31/12/2011"
    Set wbkBk = ThisWorkbook
    On Error Resume Next
    Set shtWs = wbkBk.Sheets(Format(varStartDate, "ddmmmyy"))
    On Error GoTo 0
        If Not shtWs Is Nothing Then
            varCurDate = varStartDate + 1
            intShtStart = shtWs.Index
            Set shtWs = Nothing
        Else
            varCurDate = varStartDate
            intShtStart = ActiveSheet.Index
        End If
    Do While varCurDate <= varEndDate
        With wbkBk
            .Sheets(intShtStart).Copy After:=Sheets(intShtStart)
            intShtStart = intShtStart + 1
            .Sheets(intShtStart).Activate
        End With
        If intRed = 255 Then
            intRed = 0
        Else
            intRed = 255
        End If
        With ActiveSheet
            .Name = Format(varCurDate, "ddmmmyy")
            .Range("A1").Value = Format(varCurDate, "dddd, Mmmm dd, yyyy")
[COLOR="Red"]            If varCurDate > varStartDate Then
                strFormula = "='" & Format(varCurDate - 1, "ddmmmyy") & "'!K70+K69"
                .Range("J70").Formula = strFormula
            End If[/COLOR]
            .Tab.Color = RGB(intRed, 255, 0)
        End With
        varCurDate = varCurDate + 1
    Loop
End Sub

All the best

angelocyrl · Mar 30, 2010

I tried the code but I get error. I think it is my mistake and I should have explained to you clearly. Anyway I give you further details to you.

1. I have created an excel worksheet with tab name 01 Jan 11 and renamed Template.
2. In this sheet one row contains column total i.e., J69, K69, L69, M69, N69,
O69
3. In the same sheet next row J70, K70, L70, M70, N70, O70 contains sum of previous
page total + current page column total
4. In 01 Jan 11 worksheet there would not be previous page total.
5. But from 02 Jan 11 onwards previous page total will be added in 70th row
=SUM('01Apr11'!J70+J69)
6. Likewise in 03 Jan 11 worksheet previous consolidated total of 02 Jan 11 will be
added in 70th row (i.e., 01 Apr 11 and 02 Apr 11)

So the code you have given earlier for creating multiple worksheet was perfect as I expected. Now I need to include code to change the formula as well.

Thanks a lot to AnAnalyst.

AnAnalyst · Mar 30, 2010

angelocyrl said:
I tried the code but I get error.

Do you mean you get an error message? If so, what is it?

Did you place the code in the "Template" sheet's module or a standard module?

Have you applied some protection to the sheet in the cells you want amended formulae to be written to?

3. In the same sheet next row J70, K70, L70, M70, N70, O70 contains sum of previous page total + current page column total

So you want column totals (to include the totals from the previous page) to appear in all of the specified cells? (except, obviously, the first sheet!) i.e you want a cumulative total?

5. But from 02 Jan 11 onwards previous page total will be added in 70th row
=SUM('01Apr11'!J70+J69)

The SUM() part of this formula isn't required:

Code:

='01Apr11'!J70+J69

... is fine to meet your requirement.

All the best

AnAnalyst · Mar 30, 2010

The following change should be a step closer to your actual requirement (I think) but if you answer the questions in my previous post as well, particularly regarding the error you're seeing, I can help to get this working for you.

Code:

Sub AddSheets()
    Dim varStartDate As Date, _
        varEndDate As Date, _
        varCurDate As Date, _
        shtWs As Worksheet, _
        wbkBk As Workbook, _
        intShtStart As Integer, _
        intShtCount As Integer, _
        intRed As Integer, _
        strFormula As String
    varStartDate = "01/01/2011"
    varEndDate = "31/12/2011"
    Set wbkBk = ThisWorkbook
    On Error Resume Next
    Set shtWs = wbkBk.Sheets(Format(varStartDate, "ddmmmyy"))
    On Error GoTo 0
        If Not shtWs Is Nothing Then
            varCurDate = varStartDate + 1
            intShtStart = shtWs.Index
            Set shtWs = Nothing
        Else
            varCurDate = varStartDate
            intShtStart = ActiveSheet.Index
        End If
    Do While varCurDate <= varEndDate
        With wbkBk
            .Sheets(intShtStart).Copy After:=Sheets(intShtStart)
            intShtStart = intShtStart + 1
            .Sheets(intShtStart).Activate
        End With
        If intRed = 255 Then
            intRed = 0
        Else
            intRed = 255
        End If
        With ActiveSheet
            .Name = Format(varCurDate, "ddmmmyy")
            .Range("A1").Value = Format(varCurDate, "dddd, Mmmm dd, yyyy")
            If varCurDate > varStartDate Then
[COLOR="Red"]                strFormula = "='" & Format(varCurDate - 1, "ddmmmyy") & "'!J70+J69"
                .Range("J70:O70").Formula = strFormula[/COLOR]
            End If
            .Tab.Color = RGB(intRed, 255, 0)
        End With
        varCurDate = varCurDate + 1
    Loop
End Sub

All the best

angelocyrl · Mar 31, 2010

Hai! AnAnalyst, everything works perfectly except one. I get consolidated / cumulative total in cell J70 [fx: =SUM(J69] of 01 Jan 11 but not in K70, L70, M70, N70, O70 [fx: =SUM(K69+K70) and it shows zero value.

In the next sheet 02 Apr 11, I get consolidated / cumulative total in cell J70 [fx: ='01Apr11'!J70+J69] but not in other cells K70, L70, M70, N70, O70. [fx: ='01Apr11'!K70+K69]

Other than these everything is perfect. I hope you guide in this. Merci beaucoup.

angelocyrl · Mar 31, 2010

Hai! AnAnalyst! I have corrected the problem and now its working perfectly. Thanks for all your guide and help. MERCI BEAUCOUP.

Creating multiple sheets consecutively

angelocyrl

New Member

Legacy 141710

Guest

Excel Facts

angelocyrl

New Member

AnAnalyst

Well-known Member

angelocyrl

New Member

AnAnalyst

Well-known Member

angelocyrl

New Member

AnAnalyst

Well-known Member

AnAnalyst

Well-known Member

angelocyrl

New Member

angelocyrl

New Member

Similar threads

Forum statistics

Share this page

Creating multiple sheets consecutively

New Member

Legacy 141710

Guest

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock