CODEWITHSUNDEEP
vbaexcel
Blue Book
Blue Book

Reputation: 13

Excel VBA to add weekly sheet?

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).

Upvotes: 1

Views: 2417

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12655

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

Upvotes: 2

Related Questions