Reference to Sheet Name When Copying Worksheet
 

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",ISERRO R(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(VLOOK UP(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$100 0,2,FALSE)),
C9+90,C9+91))))))

When I copy the sheet using VB (ThisWorkbook.Sheets(2).Copy
Befo=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?

--
Ken Hudson

Reference to Sheet Name When Copying Worksheet
 

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 wrote:

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",ISERRO R(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(VLOOK UP(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$100 0,2,FALSE)),
C9+90,C9+91))))))

When I copy the sheet using VB (ThisWorkbook.Sheets(2).Copy
Befo=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?

--
Ken Hudson

--

Dave Peterson

Reference to Sheet Name When Copying Worksheet
 

Hi Dave,
Are you doing this in VB?
--
Ken Hudson


"Dave Peterson" wrote:

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 wrote:

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",ISERRO R(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(VLOOK UP(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$100 0,2,FALSE)),
C9+90,C9+91))))))

When I copy the sheet using VB (ThisWorkbook.Sheets(2).Copy
Befo=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?

--
Ken Hudson

--

Dave Peterson

Reference to Sheet Name When Copying Worksheet
 

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

--
Ken Hudson


"Dave Peterson" wrote:

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 wrote:

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",ISERRO R(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(VLOOK UP(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$100 0,2,FALSE)),
C9+90,C9+91))))))

When I copy the sheet using VB (ThisWorkbook.Sheets(2).Copy
Befo=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?

--
Ken Hudson

--

Dave Peterson