Copying and creating new tabs with VBA

[RobbieL]

Hi all,

Very grateful if anyone can give me some pointers on how best to do this. I've created a worksheet that people will use to log their entry and exit times during the day.

There is a master sheet which will be blank. I then want a tab for each month named "week commencing dd-mmm-yy". Each tab needs to have a date that is 4 weeks on from the next, and ideally, won't be created until cell G33 is completed on the current tab.

Is there a way to do this relatively simply?

Many thanks in advance.

 

[al_b_cnu]

Hi,

Maybe a sheet change event such as:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim datCur As Date
Dim iSheet As Integer, iPtr As Integer
Dim sCurName As String
Dim wsCur As Worksheet

If Target.Resize(1, 1).Address = "$G$33" Then
    For iPtr = 0 To 3
        datCur = Date + (iPtr * 28)
        sCurName = "Week Commencing " & Format(datCur, "dd-mmm-yy")
        With ThisWorkbook
            Set wsCur = Nothing
            On Error Resume Next
            Set wsCur = .Sheets(sCurName)
            On Error GoTo 0
            If wsCur Is Nothing Then
                iSheet = ThisWorkbook.Sheets.Count
                Set wsCur = .Sheets.Add(after:=.Sheets(iSheet))
                wsCur.Name = sCurName
            End If
        End With
    Next iPtr
End If
End Sub