Excel VBA to add weekly sheet?

Question

The Scenario: I have an Excel sheet which has an (automatically generating) list of dates (every day in the month, except for weekends).

Those dates are in cells A2:A32 and in cells B2:B32 and C2:32 there are some extra details (which aren't really important).

I also have a Macro that activates when I click a command button. The Macro uses those dates to create a new sheet for each date.

The Macro:

Private Sub CommandButton2_Click()
Dim rng As Range
Set rng = Sheets("Sheet1").Range("A2:A32")
Dim wks As Worksheet
For Each cell In rng
    On Error Resume Next
    If cell.Value <> "" Then
        Set wks = Sheets.Add(After:=Worksheets(Worksheets.Count), Type:="C:\Users\User1\Desktop\1.xltx")
        wks.Name = cell.Value
        wks.Range("B2").Value = cell.Offset(0, 1).Value
        wks.Range("B3").Value = cell.Offset(0, 2).Value
    End If
Next cell
End Sub

The Question: How do I change the macro so that after every Friday, the macro makes a sheet with a weekly total (that uses another template and is named (if possible) week1, week2, etc).

If it helps, cell B3 has the day of the week written in text (Friday, Monday... etc).

You could just have a second sub in your workbook that looks for the day. If the day is Saturday ("after Friday"), then add new sheet with info. Would that work? Or do you also not know how to add a sheet, and get the info you want on there? Also, you'll have to have Excel opened to run this... — BruceWayne
@BruceWayne I thought of that and tried to do it but I couldn't get it to work correctly. The problem was that it would create all the regular sheets with the days of the month and THEN it would create the weekly total sheets (they weren't placed properly). I deleted it in the end because I was having trouble with save files... Could you give me some idea on how to do this? Or at least some guidelines? Thanks for the help :) — Blue Book
you don't have that code anymore do you? It sounds like you were on your way, but misplaced/misordered the macro. To create the macro, I think we'll need some more info (such as what the template you want to use looks like/is)...Perhaps you can post some sample data/screenshot of your data so we know where your data is and how to sum it? — BruceWayne
@BruceWayne This is what the dates look like: s22.postimg.org/ihfh14uy9/excel1.jpg (I have some formulas that automatically fill in the dates when I write the month in A1). The template is an accounting template but it doesn't belong to me so I don't have permission to post it on the web (sorry). Yeah what you said at the beginning sound about what happened. Sorry for sounding like a total noob but a friend asked me to do this and I have never used macros that complicated before today. — Blue Book
@BruceWayne I'm really thankful for the help btw, and you can ignore the data from columns B and C, I will add it where required later (I can do that much at least) — Blue Book

Matteo NNZ Matteo NNZ · Accepted Answer · 2015-07-24T18:26:45

You need to think "If the day is Friday, it's time to create the weekly sheet".

For the serial number of the week, there's a function of the WorksheetFunction namespace called WeekNum that takes a date in input and returns you the week number.

To say all this in your code:

For Each cell In rng
On Error Resume Next
If cell.Value <> "" Then
Set wks = Sheets.Add(After:=Worksheets(Worksheets.Count), Type:="C:\Users\User1\Desktop\1.xltx")
wks.Name = cell.Value
wks.Range("B2").Value = cell.Offset(0, 1).Value
wks.Range("B3").Value = cell.Offset(0, 2).Value

If cell.Value = "Friday" '<-- new code
    Set weekly = Sheets.Add(After:=Worksheets(Worksheets.Count),Type="your template")
    weekly.Name = "Week" + WorksheetFunction.WeekNum(cell.Value) 
End If '<-- end new code

End If
Next cell

P.s. a more realistic note. If as I understood these are working days, it's a bit risky to say "If it's Friday then the weekly". What if that week ended on Thursday and Friday is bank holiday? As well, you can't even think "if the next one is Monday" cause there might be the same problem.

Hence, not to depend on what it's written on Range("B"), I would make a check with the weeknum :

If WorksheetFunction.WeekNum(cell.Value) < WorksheetFunction.WeekNum(cell.Offset(1,0).Value) Then
'add weekly sheet

Excel VBA to add weekly sheet?

1 Answers