X amts of sheet dependant on the Month's workdays

[tuktuk]

Hey there,

I am attempting to write code that does the following:

Within a excel macro enabled file, create a new worksheet for every day of the month and names the worksheet for each day in the format of something like (“Tues. 8-28-07 CA”). That is, I will create a file called “DailySpeningListAUG.xls” that contains this code. Run the code and have it create an individual sheet for the workdays (Mon thru Friday) within August. Then make a copy of this file, run an existing code DeleteAllWorksheets and then rename “DailySpeningListCurrent.xls”.

This I’ll run this code to create all the necessary tabs for the month of September.

Each sheet added will be a copy of an existing Sheet(“SpendListTemplate”) from a Template file located at a file_path.

Here is what I have so far but I really don’t know how to create the add sheet(S) dependant on workdays in month and rename then.

It opens the Tmeplate file and copies it to the Monthly’s CURRENT file.

Thanks for the help.
Terry

Dim lookupfilename As String
lookupfilename = "C:\Users\Terry\Documents\CPS Automation Tools\OrderFormCreator\DailySpendTemplateFile.xls"        
Workbooks.Open lookupfilename
Workbooks("DailySpendTemplateFile.xls").Activate
Workbooks("DailySpendTemplateFile.xls").Sheets("SpendListTemplate").Copy _
            After:=Workbooks("DailySpendingListCurrent.xls"). _
                Sheets(Workbooks("DailySpendingListCurrent.xls").Sheets.Count)

 

[ravishankar]

Hi
try the following macro.

Sub Macro1()
a = Month(Date)
b = Choose(a, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
For c = 1 To b
Sheets("SpendListTemplate").Select
Sheets("SpendListTemplate").Copy Before:=Sheets(c)
Worksheets(c).Name = a & "-" & c & "-2007 CA"
Next c
End Sub

Run it in a workbook containing spendlisttemplate worksheet. It will choose the month from todays date and copies the template accordingly.
Ravi

 

[Norie]

Terry

Try something like this.

Sub CreateWorkdaySheets()
Dim wbNew As Workbook
Dim wbTemp As Workbook
Dim wsTemp As Worksheet
Dim I As Long

    Application.DisplayAlerts = False
    
    Set wbTemp = Workbooks.Open("C:\Users\Terry\Documents\CPS Automation Tools\OrderFormCreator\DailySpendTemplateFile.xls")
    Set wsTemp = wbTemp.Worksheets("SpendListTemplate")

    Set wbNew = Workbooks.Add(xlWBATWorksheet)

    For I = DateValue("1 August 2007") To DateSerial(2007, 9, 0)
        Select Case Format(I, "ddd")
            Case "Sat", "Sun"
                ' do nothing
            Case Else
                wsTemp.Copy after:=wbNew.Worksheets(wbNew.Worksheets.Count)
                wbNew.Worksheets(wbNew.Worksheets.Count).Name = Format(I, "dddd m-dd-yy") & " CA"
        End Select
    Next I
    
    wbNew.Worksheets(1).Delete
    wbTemp.Close
    wbNew.SaveAs "DailySpeningListAUG.xls"
    
    Application.DisplayAlerts = True
    
End Sub

Note this is hardcoded for August, but that could easily be changed to allow for user input for example.