Copy Worksheets to new workbook but break link formula.

[BrianO]

Hi all,

I'm looking to copy worksheets from one workbook to another. but there are formulas on the worksheets. I'm using

ActiveWorkbook.Worksheets("Book1").Copy After:=Workbooks("Book2.xls").Sheets(1)

to copy the sheets. Basically I am looking for book2 to be completely independant of book1. I dont want book2 to have any links to book1 but I would still like all the formulas to work book2.

Any ideas.

Thanks

BrianO

 

[Andrew Poulsom]

After copying the worksheet, choose Edit|Links and Change Source to Book2.xls by browsing. The macro recorder will give you some sample code. If you are copying multiple sheets copying them all at once will preserve the links between them.

 

[BrianO]

Thanks Andrew

Is it possible to use something like
ActiveWorkbook.WorkSheets("sheet1").Copy After:=Workbooks("Book2.xls").Sheets(1)

to select multiple sheets (I also have charts in the original workbook.)

*EDIT*****

ok figured it out

Sheets(Array("sheet1", "sheet2", "Pivots", "sheet3" _
)).Copy Before:=Workbooks("Book3").Sheets(1)


Thanks

B