Linked cells when transferring worksheets between workbooks

[TheRedCardinal]

My macro opens a template file, and copies two sheets from that template into the workbook running the macro.

The sheets have linked cells, very simple "=" links.

But I have discovered that when I move the sheets over, the links remain anchored to the original template workbook, instead of being linked to the sheet in the new workbook.

Is there a way around this when copying the worksheets over?

 

[Alex Blakenburg]

We need more information.
Are the sheets being moved just linked to each other ?
If so then you need to copy them all at once.
Do any of the sheets have tables in them ? This will prevent you from copying them all at once.

 

[TheRedCardinal]

Hi,

Thanks for your help.

Here is the code that copies the sheets:

Set WS4 = Wbk2.Sheets(WS1.Range("B5").Value)
WS4.Name = "Process"
WS4.Copy After:=WS1
Set Ws3 = Wbk.Sheets("Process")
Wbk2.Sheets("Settings").Copy After:=Wbk.Sheets(Wbk.Worksheets.Count)
Wbk2.Sheets("Input Summary").Copy After:=Wbk.Sheets("Invoice Checks")
Wbk2.Sheets("Output Summary").Copy After:=Wbk.Sheets("Input Summary")
Wbk2.Sheets("Return").Copy Before:=WS2

Wbk2.Close SaveChanges:=False

The links are between cells in "Return", and the "Input Summary" and "Output Summary" sheets.

There are no tables in place on any of the sheets.

 

[Alex Blakenburg]

You don't have a definition for Wbk or Wbk2 or WS1 or WS2, so I am unsure of what the destination is:
See if the below gives you an idea of how to proceed. You need to copy all the sheets at once so the links are to the same workbook.

In arrSht put your own sheet names.
You will then need to use Move to rearrange the sheets in the order you want them in.

Sub TestCopy()
    Dim wbNew As Workbook, wbCurrent As Workbook
    Dim arrSht As Variant
    
    Set wbCurrent = ActiveWorkbook
    Set wbNew = Workbooks.Add

    arrSht = Array("SheetA", "Sheet 2", "Sheet3")
    
    wbCurrent.Worksheets(arrSht).Copy after:=wbNew.Sheets(1)
    
End Sub

 

[TheRedCardinal]

Thanks,

I get a Type Mismatch error in the copy line here:

Set Wbk2 = Workbooks.Open(Templatefile)

'Copy Sheets, and populate cells

CopySheets = Array(Wbk2.Sheets(WS1.Range("B5").Value), Wbk2.Sheets("Settings"), Wbk2.Sheets("Input Summary"), Wbk2.Sheets("Output Summary"), Wbk2.Sheets("Return"))

Wbk2.Worksheets(CopySheets).Copy after:=Wbk.Sheets("Launch")

The 5 sheets are all correct and in the array.

Wbk is my calling workbook where the macro is stored, and has a sheet called "Launch".

 

[Alex Blakenburg]

Do you have a line that says "Set Wbk = ThisWorkbook" ?
Ideally show me all the code.