Duplicate Existing Worksheet and Name Tab

[Papi]

I am trying to to take a formatted worksheet called Blank which sits on the far left side and copy it to the far right side (next to the last working sheet) and change cell D1 to reflect seven days more than the last sheet i.e. Jul 25 would now becom Aug 1 etc.

Each tab is named the month and day using "mmm d" which is sitting as a formula in Cell H1 named Tab_Name =CONCATENATE(TEXT(D1,"mmm")," ",TEXT(D1,"d")) so would like to have the tab named using that format.

Any help would be greatly appreciated.

 

[VoG]

Try

Sub CopySheet()
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
Range("D1").Value = Range("D1").Value + 7
ActiveSheet.Name = Range("H1").Text
End Sub

 

[Papi]

Hello Vog,

Thanks very much. Can you help with one last part of the entry. When your code runs can it put the new date in Cell D1 of the new sheet as that cell feeds other cells in the new worksheet.

 

[VoG]

The line in red does that

Sub CopySheet()
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
Range("D1").Value = Range("D1").Value + 7
ActiveSheet.Name = Range("H1").Text
End Sub

I just tested to make sure that it does.

 

[Papi]

Hello VoG,

It changes in the blank form but not in the new form.

 

[VoG]

For me it doesn't write anything to the Blank sheet. Maybe this (also tested and working)

Sub CopySheet()
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Range("D1").Value = Range("D1").Value + 7
    .Name = Range("H1").Text
End With
End Sub

 

[Papi]

Hello VoG,

Sorry for the confusion. If the last worksheet is Jul 25 on the tab and July 25, 2011 in cell D1 of the same sheet I need the new worksheet to reflect seven days ahead i.e. Aug 1 in the tab and August 1, 2011 in cell D1 of the new worksheet and the dates always controlled by the newest worksheet. Would I need to enter somehting in the Blank worksheet?

 

[VoG]

Sorry, now I understand

Sub CopySheet()
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Range("D1").Value = .Previous.Range("D1").Value + 7
    .Name = Range("H1").Text
End With
End Sub

 

[Papi]

The date in cell D1 the new worksheet works fine. But the cell name is coming from the Blank worksheet instead of cell D1 of the new worksheet. Sorry for the trouble.

 

[VoG]

Try

Sub CopySheet()
Sheets("Blank").Copy after:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
    .Range("D1").Value = .Previous.Range("D1").Value + 7
    .Name = Format(Range("D1").Value, "mmm d")
End With
End Sub