CODEWITHSUNDEEP
excelvbacopyformulas
Eric Bauman
Eric Bauman

Reputation: 13

Copying worksheets to another workbook, have references point to new workbook

Here's the situation I face:

I have a workbook with two worksheets, "Survey" and "Configuration". The Configuration worksheet has cells that link to the Survey worksheet.

I am trying to copy those worksheets into a new workbook. Once I have done the copy, though, the cells in the Configuration tab in the new workbook point back to the old workbook. One solution that I thought of was to just copy the values to the new worksheet/new workbook, but my client does not want that: they want the formulas to still be there in case they want to update some of the data on the Survey tab and have it carry forward to the configuration.

Is there a way to copy a worksheet such that any cell references stay the same (i.e. still point to "Survey!A1" instead of pointing back to "OldWorkbook!Survey!A1"?

EDIT I am trying to use the Worksheets(Array(...)).Copy method. The problem I am now running into is that I have an array of strings with the names of the worksheets that are common to both of the source workbooks. If I substitute the array for Array(...), the code blows up. Unfortunately, I don't know ahead of time which sheets will be common to the source workbooks, so I cannot hardcode a list for the Array function, and I've tried creating a string that looks like an argument list for Array and using that, but the code didn't like that, either.


    'Initialize indexes and arrays and stuff
    intSheetsInCommonIndex = 1
    intDiscrepanciesIndex = 1
    intSheetsInNPEIndex = 1
    lngDiscrepancyBackColor = RGB(202, 6, 95)
    Application.ScreenUpdating = False

    For intInner = 1 To 40
        strSheetsInCommon(intInner) = ""
        strSheetsInNPE(intInner) = ""
    Next intInner

    'Set up the pointers to the two source workbooks.
    Set xlNPEWorkbook = Application.Workbooks.Open(Sheets("Combine PE Workbooks").txtNPEDocLocation)
    Set xlSPEWorkbook = Application.Workbooks.Open(Sheets("Combine PE Workbooks").txtSPEDocLocation)

    'Now, fill the strSheetsInNPE array
    For Each xlNPECurrentSheet In xlNPEWorkbook.Sheets
        strSheetsInNPE(intSheetsInNPEIndex) = xlNPECurrentSheet.Name
        intSheetsInNPEIndex = intSheetsInNPEIndex + 1
    Next xlNPECurrentSheet

    'Now go through the SPE document.  If the workhseet's name is in strSheetsInNPE, add that name to strSheetsInCommon.
    For Each xlSPECurrentSheet In xlSPEWorkbook.Sheets
        For intInner = 1 To intSheetsInNPEIndex
            If xlSPECurrentSheet.Name = strSheetsInNPE(intInner) Then
                strSheetsInCommon(intSheetsInCommonIndex) = xlSPECurrentSheet.Name
                intSheetsInCommonIndex = intSheetsInCommonIndex + 1
                Exit For
            End If
        Next intInner
    Next xlSPECurrentSheet

    'Turn alerts off (so that the messages about named ranges don't show up) and turn on the hourglass.
    Application.DisplayAlerts = False
    Application.Cursor = xlWait

    'Create the combined document
    Set xlCombinedWorkbook = Workbooks.Add
    xlCombinedWorkbook.UpdateLinks = xlUpdateLinksNever

    'Go through the NPE document and add all worksheets from there to the new workbook.

    xlNPEWorkbook.Worksheets(Array(strSheetsInCommon)).Copy xlCombinedWorkbook.Sheets(1)

If anybody has any suggestions on how I can use my array of sheet names to do the multiple sheet copy, I'd greatly appreciate it.

Thank you for your help!

Upvotes: 1

Views: 1521

Answers (1)

basodre
basodre

Reputation: 5770

Perhaps I am overlooking some details, but why not simply use SaveAs to save the old workbook to a new workbook? References will be in place and you will have a new workbook.

Upvotes: 1

Related Questions