Reference to Sheet Name When Copying Worksheet

  • Thread starter Thread starter Ken Hudson
  • Start date Start date
K

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?
 
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????
 
Back
Top