Copying sheets to another workbook-without links

[Kinney]

How do I copy a sheet to another workbook without the link to the original and still maintain the formula?

 

[crimlet]

Is that not the Paste special formula function ?

 

[Kinney]

Yes, that is it, but I would like to paste multiple sheets at a time, and there is no option for paste special when copying sheets from one workbook to another. Any ideas?

 

[crimlet]

How many sheets ? and what sort of volume of repitition are we talking?

 

[Kinney]

There are a total of 10 sheets, which get information for the formulas from other sheets in the same workbook. I have 250 workbooks to create these identical 10 sheets in. All workbooks are of the same format (the data is on the same sheet label, same cell identifier, just different data). When I copy them to another workbook, the links are posted in the formulas. I would like the sheets to copy over without the links and start using the identified cell and sheet as listed in the original formula. Thanks for your help!

 

[Egad]

Try defining your old and new filenames, copy-paste the sheet, then add a variation of this to your macro: (use record-macro to get the syntax right)

ActiveWorkbook.ChangeLink Name:="oldfilename.xls", NewName:= _
"\foldernamenewfilename.xls", Type:=xlExcelLinks

 

[Kinney]

That would work, but the sheets are rather large and contain many formulas, is there a way to just specify "remove links" from the copied sheet?

 

[Egad]

I wish it was that easy. ;o) If you want to do it manually then Edit-Links-Change Source and replace with the new (active workbook) filename.

 

[Kinney]

Thanks, I will try that.

 

[AllyCat]

Open both workbooks.....right click on the sheet tabs you want to copy and choose a workbook to paste them into. Make sure you tick the create a copy checkbox or you'll move the original.