Create new worksheet using name of current sheet + 7 days

[jeffdolton]

Good day,

I attach the workbook I'm working on test 1.xlsm

This is a simple timesheet. I would like a button on the current sheet to say “Create Next Sheet” or something similar. On clicking the button, the next sheet would then:

• Be copied from the current sheet.
• Be renamed in the format DD-MM-YYYY for the next date in sequence, i.e + 7 days.
• The formulae for the blue cells in B1 and B6 to be updated to reference the previous sheet.

I hope this makes sense. Thanks for looking at this.

 

[Peter_SSs]

I would like a button on the current sheet

Instead of having buttons on all the sheets, I would suggest setting it up so that a double-click on, say, cell A1 will do the required steps. That is what my suggestion below does.

Be renamed in the format DD-MM-YYYY

I have included that though I note that your current sheets are not named like that.

Try this with a copy of your workbook. The code goes in the ThisWorkbook module in the vba window.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  If Target.Address = "$A$1" And IsDate(Sh.Name) Then
    Cancel = True
    Sh.Copy After:=Sh
    With ActiveSheet
      On Error Resume Next
      .Name = Format(DateAdd("d", 7, Sh.Name), "dd-mm-yyyy")
      On Error GoTo 0
      .Range("B1").Formula = "='" & Sh.Name & "'!I13"
      .Range("B6").Formula = "='" & Sh.Name & "'!H6 +1"
    End With
  End If
End Sub

 

[jeffdolton]

Instead of having buttons on all the sheets, I would suggest setting it up so that a double-click on, say, cell A1 will do the required steps. That is what my suggestion below does.


I have included that though I note that your current sheets are not named like that.

Try this with a copy of your workbook. The code goes in the ThisWorkbook module in the vba window.

View attachment 53685

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  If Target.Address = "$A$1" And IsDate(Sh.Name) Then
    Cancel = True
    Sh.Copy After:=Sh
    With ActiveSheet
      On Error Resume Next
      .Name = Format(DateAdd("d", 7, Sh.Name), "dd-mm-yyyy")
      On Error GoTo 0
      .Range("B1").Formula = "='" & Sh.Name & "'!I13"
      .Range("B6").Formula = "='" & Sh.Name & "'!H6 +1"
    End With
  End If
End Sub

Many thanks Peter. The code works well except for cell B6. After creating many new tabs, this cell isn't taking the name of the current tab and adding 1. It seems to be behind by one month.

 

[Peter_SSs]

The code works well except for cell B6. After creating many new tabs, this cell isn't taking the name of the current tab and adding 1.

That's not what you asked for. You asked for

B6 to be updated to reference the previous sheet.

I used the same formula format that your existing sheets had. For example, in your file in cell B6 of sheet '28-11-21' the formula is

='21-11-21'!H6+1

That is, it is taking the H6 value from the previous week's sheet and adding 1.

Can you explain again then what, exactly, should be in B6 for a particular week?

 

[jeffdolton]

That's not what you asked for. You asked for


I used the same formula format that your existing sheets had. For example, in your file in cell B6 of sheet '28-11-21' the formula is

='21-11-21'!H6+1

That is, it is taking the H6 value from the previous week's sheet and adding 1.

Can you explain again then what, exactly, should be in B6 for a particular week?

Sorry if I confused you. Cell B6 in the new worksheet should be the name (date) in the previous sheet plus one day. So new worksheet 09-02-2022 should read 03-02-2022 in cell B6.

Many thanks, Jeff

 

[jeffdolton]

Oops, my mistake - everything is fine, thank you!

 

[Peter_SSs]

Oops, my mistake - everything is fine, thank you!

Glad to hear.

Was it because the sample file had no January sheets & skipped straight to February?

 

[jeffdolton]

Yes! Schoolboy error.

 

[Peter_SSs]

Yes! Schoolboy error.

Main thing is that it is resolved.

BTW, I changed the 'Mark as solution' from post 8 to 2 as it is supposed to mark the post that actually contains the solution, not just that the thread is regarded as solved. Bit different to some other forums.