Reference to Sheet Name When Copying Worksheet

[Ken Hudson]

In a macro I am copying a master worksheet to a new workbook. In some of the
cells of the master I have nested if statements with Vlookups. Sample:

=IF(C9="","",IF(AND(TEXT(C9+90,"ddd")="Sat",ISERROR(VLOOKUP(C9+92,
Holidays!$A$1:$B$1000,2,FALSE))),C9+92,IF(TEXT(C9+90,"ddd")="Sat",
C9+93,IF(AND(TEXT(C9+90,"ddd")="Sun",ISERROR(VLOOKUP(C9+91,
Holidays!$A$1:$B$1000,2,FALSE))),C9+91,IF(TEXT(C9+90,"ddd")="Sun",
C9+92,IF(ISERROR(VLOOKUP(C9+90,Holidays!$A1:$B$1000,2,FALSE)),
C9+90,C9+91))))))

When I copy the sheet using VB (ThisWorkbook.Sheets(2).Copy
Before:=ActiveWorkbook.Sheets(1)), the sheet name reference (Holidays)
expands to include the master workbook name and path. I want the newly
created worksheet to keep the reference "Holidays1" just the way it is.
Have I explained this well enough for someone to assist please?

 

[Dave Peterson]

If I'm only copying a cell (or two), I'll copy from the formulabar and paste
into the formulabar.

If I'm copying lots of cells, I'll change my formulas to text, do the
copy|paste, and then change the text back to formulas.

select the range to copy
edit|replace
what: =
with: $$$$$
replace all

do the copy|paste

select the pasted range
edit|replace
what: $$$$$
with: =
replace all

And remember to do the same thing to the source range--or close without
saving????

 

[Ken Hudson]

Hi Dave,
Are you doing this in VB?

 

[Ken Hudson]

I think I have it adapted to VB code.
Thanks for the solution!