Excel VBA Copy Sheet without original workbook reference

Question

I have a routine that is copying various sheets from many workbooks and pasting these sheets in what I am calling a 'master' workbook (which contains the different sheets from the different workbooks).

The code works well, however it is also keeping a reference to the original sheet with the usual 'extra' for example =SUM([Book1]Sheet1!C13:G13) (to keep the example simple).

The worksheets I am copying need to be copied in the exact same formatting where I am using the following command:

Dim WS_Count As Integer
WS_Count = wb.Worksheets.Count
For c = 1 To WS_Count
    If wb.Sheets(c).Name <> "TEST" Then
        wb.Sheets(c).Copy Before:=master.Worksheets(master.Sheets.Count)
    End If
Next

The Copying and merging of documents works very well however as I am also copying some summary sheets from the workbooks, these contain internal reference to sheets and I am encountering difficulties in copying without the original workbook reference [Book1]. I would not know the file name of the workbook while I am working on the master workbook because there are many source workbook documents.

My question is, Is there a way to copy a sheet with all of its formatting without copying the cell workbook reference?

I have also tried all variations of Paste / Paste Special however this either loses the worksheet formatting or still retains the other workbook reference.

I wish to avoid having to find and replace any string that contains [... .xls] as its not an elegant solution. Any pointers in the right direction would be appreciated.

Hi @JimmySmith: Modified original post. The code works well and does what I need exactly however the only problem is that the output master file includes references to the original workbook which needs to be removed — Erika
Just to confirm, you're wanting to paste the data and retain formatting, minus formulas referring to other sheets? You can paste the values like so targetRange.PasteSpecial xlPasteFormats targetRange.PasteSpecial xlPasteValues — Jimmy Smith
Note that you can also just relink the link from the source workbook back to the file you have copied to to kill the links. — brettdj

MP24 MP24 · Accepted Answer · 2014-06-20T14:24:35

If you copy all your sheets at once, the formula references will point to the copied sheets instead of the source sheets. You will achieve the same functionality as selecting multiple sheets using Shift with the help of the following code:

wb.Worksheets(Array("Sheet1", "Sheet2")).Copy Before:=master.Worksheets(master.Worksheets.Count)

So if your worksheet names are fixed, you can replace the inside the Array() function call, else you would need to create an appropriate array inside your For c = 1 To WS_Count loop and call the copy code once afterwards:

Dim ws() As String ' declare string array
ReDim ws(wb.Worksheets.Count) As String ' set size dynamically

Dim counter As Long ' running counter for ws array
counter = 1

For c = 1 to WS_Count
    If wb.Worksheets(c).Name <> "TEST" Then
        ws(counter) = wb.Worksheets(c).Name
        counter = counter + 1
    End If
Next

ReDim Preserve ws(counter) As String ' Strip of superfluous empty array entries (i.e. TEST sheets
wb.Worksheets(ws).Copy Before:=master.Worksheets(master.Worksheets.Count)

Note that this code is untested.

Excel VBA Copy Sheet without original workbook reference

1 Answers