Copy Worksheet to another Workbook without references

[JerikoOne]

Hi,

I have a workbook functioning as a template with several sheets, one of which with cell references to the others. I need to copy this sheet to other workbooks, which have the same structure as the template. However, upon pasting it, every cell of the template sheet gets a reference to the template file, which isn't wished. What I do need is an exact copy of the template sheet, with no further references added.

Here's the code snippet I use so far:

Workbooks.Open Filename:=sFolCalc & "\" & sFilename, UpdateLinks:=0
Sheets("Export COPA").Delete        ' Delete old sheet
Windows(sNameCalcT5).Activate       ' Copy new sheet
ActiveSheet.Protect Contents:=True
Sheets("Export COPA").Copy Before:=Workbooks(sFilename).Sheets(9), UpdateLinks:=0

"Export COPA" in sNameCalcT5 is the template sheet, which needs to be inserted in sFilename.

Greets, Chris

 

[WalkerEvans]

Try this:

Workbooks.Open Filename:=sFolCalc & "\" & sFilename
Select Case Filename:=sFolCalc & "\" & sFilename.UpdateLinks
Case xlUpdateLinksNever
End Select

 

[combo]

What do you expect to get in copied worksheet: values or local formulas? If values, break links, if references to local worksheet, search for "[*]" and replace by "". Record macro to see the rough code.

combo

 

[RonRepp]

Sub ChangeLinks()
Dim OldWB As String
OldWB = "[MyWorkbook.xls]"

    Cells.Replace What:=OldWB, Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False
End Sub

Just remember that all worksheets that are linked in the old workbook must be in the new workbook or else the open dialog box will keep opening for you to update the new link.

BTW, most of this was done simply with an Excel user's best friend; the Macro recorder.


I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.