Consulting

Results 1 to 2 of 2

Thread: Copy Sheet to New WB but Macros still point to sheet in WB

  1. #1

    Copy Sheet to New WB but Macros still point to sheet in WB

    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.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,901
    Location
    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
    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •