Macro To Create Worksheets & Add Dates Closed

Question

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

venkat1926 · Answer

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

venkat1926 · Answer

this is for June 
if you want for July change the statement
mmonth=6 
into 
mmonth=7

cabarnes · Answer

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

venkat1926 · Answer

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.

Macro To Create Worksheets & Add Dates

4 responses

Similar discussions

Subscribe To Our Newsletter!