Export worksheets to new file VBA to 'Break Links' on export?

Hi Guys,

I'm using this code to export 3 select worksheets in my workbook to a new file which in essence works great.

Please Login or Register  to view this content.

The problem I'm having now though is that when the sheets are exported, as the 3 sheets refer to eachother (data on one sheet, mapping on the 2nd and pivot on the other), the new file ends up with formula links to the original workbook, which is not ideal.

Does anyone have any suggestions how I can resolve this issue with VBA or otherwise?

Thanks,


Spoonman

It says "Run-time error '1004' We can't make this change for the selected cells because it will affect a pivot table. Use the field list to change the report. If you are trying to insert or delete cells, move the pivot table and try again.

It's actually half working now, it creates the new file but saves it as 'book1' instead of the name and cell reference as it's suppose to.

Also I think I see the problem now, the links are broken in the formulas but the pivot is still referencing the original workbook.

Thanks,

This is much closer and may suffice as at least there are no links to the source workbook however it would be desirable if it could stay as a pivot as this just shows the pivot as a data table and gets rid of the functionality.

Maybe it's not possible to do this?

Thanks for all your help