X amts of sheet dependant on the Month's workdays

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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


Code:
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)
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
try the following macro.
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Terry

Try something like this.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top