Excel VBA Copy Sheet without original workbook reference

Erika picture Erika · Jun 20, 2014 · Viewed 12.4k times · Source

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.

Answer

MP24 picture MP24 · Jun 20, 2014

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.