Macro To Create Worksheets & Add Dates

[Closed]
Report
Posts
2
Registration date
Saturday June 9, 2012
Status
Member
Last seen
June 10, 2012
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have a worksheet (Excel 2007) that has two master pages with data, Weekday, and Saturday. Every month the tab Weekday is copied and renamed for for the date, and the same with Saturday (Sunday is not being used). For example, this month a tab June 1 (Weekday), June 2 (Saturday), June 4 (Weekday), June 5 (Weekday), etc, for the complete month.

Then on each tab, a cell contains the same date as the tab name. This is done on a monthly basis, for every month, manually.

I've seen a macro that copies tabs and puts the name of the tab into a cell on that tab, but it was the same tab being duplicated. I have two tabs (Weekday & Saturday), which I need to copy for the respective dates, Weekday 5 times (Mon - Fri) and then Saturday, then repeat for the dates in a given month.

Basically, tell it to create tabs for June 1 to June 30, or July 1 to July 31, etc.

Any help would be greatly appreciated.

Thanks,
Craig

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
let me see what you want is
you want to add worksheets for each weekday and Saturday in a month and give the names to the sheets correspondinglyl

open a new workbook
in the vb editor add this macro "daillytabs"
save the file
and run the macro . do you get what you want
if the name of the sheet should be slightly modified then
modify the statement (the text part of it)
WorksheetFunction.Text(wweekday(k), "mmm d yy")


the macro is


Sub dailytabs()
Dim ddate() As Long, j As Long, k As Long, mmonth As Long, wweekday() As Date
mmonth = 6
j = Day(DateValue("2012," & mmonth + 1 & ",1") - 1)
ReDim ddate(1 To j)
ReDim wweekday(1 To j)
For k = 1 To j

wweekday(k) = DateValue("2012," & mmonth & "," & k)
If Weekday(wweekday(k)) = 7 Then GoTo nextk
Worksheets.Add
ActiveSheet.Name = WorksheetFunction.Text(wweekday(k), "mmm d yy")
nextk:
Next k

End Sub
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
this is for June
if you want for July change the statement
mmonth=6
into
mmonth=7
Posts
2
Registration date
Saturday June 9, 2012
Status
Member
Last seen
June 10, 2012

It added the tabs, but they were all empty. As stated above, the 'Weekday ' tab needs to be copied for each weekday date and the Saturday' tab copied for each Saturday date.

I have it making the tabs for each weekday, but don't know how to make the Saturday tabs. The code is below. I have a sepaarte tab that has the weekday dates that I manually entered on the 'Data' tab.

Sub July()
'
' July Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Application.ScreenUpdating = False
Sheets("Create Monthly Sheets").Select
Sheets("Data").Visible = True
Sheets("Create Monthly Sheets").Select
Sheets("MASTER").Visible = True
Sheets("MASTER").Select
Sheets("SATURDAY MASTER").Visible = True

Sheets("Data").Select
' Determine how many weekday dates are on Data sheet
FinalRow = Range("B65000").End(xlUp).Row
' Loop through each weekday date on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Data").Select
WeekdayDate = Range("B" & x).Value
' Make a copy of MASTER and move to end
Sheets("MASTER").Copy After:=Sheets(LastSheet)
' rename the sheet and set K2 = to the weekday date
Sheets(LastSheet + 1).Name = WeekdayDate
Sheets(WeekdayDate).Select
Range("K2").Value = WeekdayDate
Next x

Sheets("MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SATURDAY MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Create Monthly Sheets").Select
ActiveWindow.SelectedSheets.Visible = False

End Sub
Sub August()
'
' August Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Application.ScreenUpdating = False
Sheets("Create Monthly Sheets").Select
Sheets("Data").Visible = True
Sheets("Create Monthly Sheets").Select
Sheets("MASTER").Visible = True
Sheets("MASTER").Select
Sheets("SATURDAY MASTER").Visible = True

Sheets("Data").Select
' Determine how many weekday dates are on Data sheet
FinalRow = Range("C65000").End(xlUp).Row
' Loop through each weekday date on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Data").Select
WeekdayDate = Range("C" & x).Value
' Make a copy of MASTER and move to end
Sheets("MASTER").Copy After:=Sheets(LastSheet)
' rename the sheet and set K2 = to the weekday date
Sheets(LastSheet + 1).Name = WeekdayDate
Sheets(WeekdayDate).Select
Range("K2").Value = WeekdayDate
Next x

Sheets("MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SATURDAY MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Create Monthly Sheets").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub SeptemberWeekday()
'
' SeptemberWeekday Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Application.ScreenUpdating = False
Sheets("Create Monthly Sheets").Select
Sheets("Data").Visible = True
Sheets("Create Monthly Sheets").Select
Sheets("MASTER").Visible = True
Sheets("MASTER").Select
Sheets("SATURDAY MASTER").Visible = True

Sheets("Data").Select
' Determine how many weekday dates are on Data sheet
FinalRow = Range("D65000").End(xlUp).Row
' Loop through each weekday date on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Data").Select
WeekdayDate = Range("D" & x).Value
' Make a copy of MASTER and move to end
Sheets("MASTER").Copy After:=Sheets(LastSheet)
' rename the sheet and set K2 = to the weekday date
Sheets(LastSheet + 1).Name = WeekdayDate
Sheets(WeekdayDate).Select
Range("K2").Value = WeekdayDate
Next x

Sheets("MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SATURDAY MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Create Monthly Sheets").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub OctoberWeekday()
'
' OctoberWeekday Macro
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Application.ScreenUpdating = False
Sheets("Create Monthly Sheets").Select
Sheets("Data").Visible = True
Sheets("Create Monthly Sheets").Select
Sheets("MASTER").Visible = True
Sheets("MASTER").Select
Sheets("SATURDAY MASTER").Visible = True

Sheets("Data").Select
' Determine how many weekday dates are on Data sheet
FinalRow = Range("E65000").End(xlUp).Row
' Loop through each weekday date on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Data").Select
WeekdayDate = Range("E" & x).Value
' Make a copy of MASTER and move to end
Sheets("MASTER").Copy After:=Sheets(LastSheet)
' rename the sheet and set K2 = to the weekday date
Sheets(LastSheet + 1).Name = WeekdayDate
Sheets(WeekdayDate).Select
Range("K2").Value = WeekdayDate
Next x

Sheets("MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SATURDAY MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Create Monthly Sheets").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub NovemberWeekday()
'
' NovemberWeekday Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Application.ScreenUpdating = False
Sheets("Create Monthly Sheets").Select
Sheets("Data").Visible = True
Sheets("Create Monthly Sheets").Select
Sheets("MASTER").Visible = True
Sheets("MASTER").Select
Sheets("SATURDAY MASTER").Visible = True

Sheets("Data").Select
' Determine how many weekday dates are on Data sheet
FinalRow = Range("F65000").End(xlUp).Row
' Loop through each weekday date on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Data").Select
WeekdayDate = Range("F" & x).Value
' Make a copy of MASTER and move to end
Sheets("MASTER").Copy After:=Sheets(LastSheet)
' rename the sheet and set K2 = to the weekday date
Sheets(LastSheet + 1).Name = WeekdayDate
Sheets(WeekdayDate).Select
Range("K2").Value = WeekdayDate
Next x

Sheets("MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SATURDAY MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Create Monthly Sheets").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub
Sub DecemberWeekday()
'
' DecemberWeekday Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Application.ScreenUpdating = False
Sheets("Create Monthly Sheets").Select
Sheets("Data").Visible = True
Sheets("Create Monthly Sheets").Select
Sheets("MASTER").Visible = True
Sheets("MASTER").Select
Sheets("SATURDAY MASTER").Visible = True

Sheets("Data").Select
' Determine how many weekday dates are on Data sheet
FinalRow = Range("G65000").End(xlUp).Row
' Loop through each weekday date on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Data").Select
WeekdayDate = Range("G" & x).Value
' Make a copy of MASTER and move to end
Sheets("MASTER").Copy After:=Sheets(LastSheet)
' rename the sheet and set K2 = to the weekday date
Sheets(LastSheet + 1).Name = WeekdayDate
Sheets(WeekdayDate).Select
Range("K2").Value = WeekdayDate
Next x

Sheets("MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("SATURDAY MASTER").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Create Monthly Sheets").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub


Craig
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
did you try my smaller macro in an new workbook.



however I am sending a workbook which can be downloaded from here
http://speedy.sh/hEMn6/cabernes.xlsm
an undo macro is also added

you find tabs are opened for all the days except Sundays.

perhaps you may not see all the tabs. if you right click the series of arrows to the left of the first tab you can see all the sheets (if necessary you have to click "more sheets" at the bottom of this list.


and also the name of the worksheets is copied in A1 of each sheet.
only other tab is that of Sheet1 .

if you want to test first run "undo" and then run "dailytabs"

what more do you want.