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.

 

[Papi]

Now it is giving a VBA Error 400 and the new filename is Blank (2). The date is still fine in the new worksheet but the naming is out.

 

[VoG]

I don't get an error and the sheet is named correctly

Excel Workbook
	D	E	F	G	H
1	12/08/2011				Aug 12
Aug 12

 

[VoG]

Before you run the macro, make sure that D1 of the last sheet contains a date.

 

[Papi]

Hello Peter,

The last worksheet has July 25 in cell D1 and the Blank worksheet does not have anything in D1. The renaming of D1 works great. The confusion is where it is getting the name for the tab as it is not going to Blank (2). On one of the previous codes it was naming the tab with what was in the Blank cell. What I need is for it to name it from the new worksheet cell D1.

 

[VoG]

What I need is for it to name it from the new worksheet cell D1.

That is exactly what it does if you read the code. But the date is the date in the previous sheet D1 plus 7. If that isn't a valid date I would expect the code to error.

 

[Papi]

The date is valid as I tested it by running a quick formula = D1+& and it returns August 1 from July 25. With my limited understanding of such code it appears that your code is correct but for some reason it seems to be naming the file tab with the blank file rather than the new worksheet. That appears true as it is calling the new file Blank (2) as though it is duplicating that first.

 

[VoG]

It calls it Blank (2) because that is Excel's default if you copy a sheet and don't rename it.

I don't know what else I can do - it works for me.

I've uploaded my test file http://www.box.net/shared/0348jay53i3ve87rrifx

 

[Papi]

I see where I may be going wrong as your file is perfect. I may have not entered your code in the correct area as I see yours shows Option Explicit and mine does not. What am I doing wrong?

 

[Papi]

I entered mine by right clicking the tab and going view code and pasting it there.

 

[VoG]

The code goes in a regular module. I think you must have it in a sheet's code module.

Remove your existing code. Press ALT + F11 to open the VBE, select Module from the Insert menu and paste the code in there.

Mine has Option Explicit at the top because I have Require Variable Declaration checked in the VBE Tools > Options.