PDA

View Full Version : Copy Sheet to New WB but Macros still point to sheet in WB



PlasticMacro
09-20-2019, 12:18 PM
I have a main Workbook called MASTER WORKBOOK that has many sheets. One sheet, SETUP&INVENTORY, has a macro that attaches said sheet to an email with a new file name (just the single sheet, Macro 1)). That new sheet has some cells referencing another sheet in MASTER WORKBOOK. When I "Break Links" it kills the macros I have on the sheet, even though the VBA code is transferred with the sheet to the new file. It seems to me that the macros on the new sheet (2 and 3) are linked to the VBA code on the sheet in the MASTER WORKBOOK. How do I keep this from happening?

For the solution, I need to be able to click Macro 1) to attach the sheet to an email. After it's saved in a new location, I need to be able to use Macros 2) and 3) without them linking back to MASTER WORKBOOK.

I have attached MASTER WORKBOOK and an already saved off SETUP&INVENTORY sheet name 401-010-4172.

SamT
09-21-2019, 07:28 AM
Use explicit references in the code on the original version of "New Sheet"
Sub test()
Dim MastBk As Workbook
Dim Refsht As Worksheet

If Not ThisWorkbook.Name = "Master Workbook.xlsm" Then
Set MastBk = Workbooks("Master Workbook.xlsm")
Else
Set MastBk = ThisWorkbook
End If

Set Refsht = MastBk.Sheets("Reference Sheet")

'In Code refer to certain cells as Refsht.Range("A1") etc.

End Sub