Copy Worksheet w/o Links

[jmnekrkt]

I have a workbook w/several sheets. I need to copy one sheet to a new book but that sheet is linked to others in the original workbook. How can I copy the sheet w/values instead of the links?

Thank you

 

[dwilson01]

You could copy all the cells to a new workbook using the pastespecial:=xlvalues

This is a simple macro I recorded, it could be made more general for use on multiple files, etc.

    Cells.Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlValues

If this doesn't do the trick, let me know

Dave

 

[jmnekrkt]

I've used the pastespecial value but I lose formatting. I can use pastespecial format to get back most of the formatting but it does not touch column widths. When I use pastespecial columnwidth, I get an error. All suggestions are greatly appreciated.

 

[dwilson01]

Try copying the worksheet, move the linked columns and copy/pastespecial back to original columns. There may be other ways to solve your problem, but I can't think of one that breaks links and maintains formatting.

 

[aMember]

Turn off display alerts.
Copy the pages with the links.
Select the receiving workbook and change the links to point to itself:

ActiveWorkbook.ChangeLink Name:="Receiving_WB.xls", NewName:= _
"Path_To_Receiving_WB.xls", Type _
:=xlExcelLinks

 

[jmnekrkt]

I am unable to change the links to put to itself because the links point to a different sheet in the original workbook.

 

[Kaah]

you might find that each of the links in the copy-to sheet are being maintained in each cell's formula. it might be somthing in the format of
"=[thatWorkbook!thatWorksheet]C5"

<sorry- been a long time since messed with this stuff- not exactly sure that it's correct- but the idea is right>

use a find and replace to get rid of the external workbook/worksheet references in the copy-to sheet after copying the contents of the copy from. This has worked for me in the past although your exact conditions may be different...replace the external reference from each cell with &quot;&quot;.

also- the pastespecial columnwidths certainly does work- you're doing something wrong. make sure that your selections are right etc...you should include the error message so that the forum might be able to help you!
You might get an error from this if you have merged cells...

hope this helps,

k.