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!)

wrote:

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

--

Dave Peterson

Dave's suggestion is the way

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ups.com...
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