Copying worksheet to all workbooks with fixed references

  • Thread starter Thread starter ogocmen
  • Start date Start date
O

ogocmen

I have a worksheet that I need to add to about a 100 excel workbooks
under a folder with the same structure and format.

So, I used Ron's code (Thanks!) to copy the worksheet to all the files
under that directory. However, I need to copied worksheet to have the
fixed references.

For example, cell A1 in the new sheet copied into the files must show
the value of A3 from second sheet (=Sheet2!A3). However when the sheet
is copied the references are not absolute and it shows
'[Macro.xls]Sheet2!$A$3' and pointing the cell reference to the sheet I
run the macro from.

is there anyway to make the references on the sheets copied
absoulute(fixed)?

thanks
 
I change all my formulas to text, do the copy|paste and then change them back to
formulas.

Select the range (all the cells???)
edit|replace
what: =
with: $$$$$
replace all

Edit|copy
edit|paste

Then do the reverse
select the range
edit|replace
what: $$$$$
with: =
replace all

(in both spots!)

I have a worksheet that I need to add to about a 100 excel workbooks
under a folder with the same structure and format.

So, I used Ron's code (Thanks!) to copy the worksheet to all the files
under that directory. However, I need to copied worksheet to have the
fixed references.

For example, cell A1 in the new sheet copied into the files must show
the value of A3 from second sheet (=Sheet2!A3). However when the sheet
is copied the references are not absolute and it shows
'[Macro.xls]Sheet2!$A$3' and pointing the cell reference to the sheet I
run the macro from.

is there anyway to make the references on the sheets copied
absoulute(fixed)?

thanks
Click to expand...
 
Dave's suggestion is the way

If you have problems to change the code example post back then
I will help you
 
Back
Top