How to make macro button reference new workbook after copy/paste-ing parent sheet

[uahmed318]

Hello all,

I've been working on a Table of Contents (TOC) sheet with hyperlinks that can be copy and pasted into any workbook and will automatically populate with the sheet names of that workbook. It can also duplicate any sheet named "Master Template" with a button that references a macro that is embedded in the sheet. This will ask for a sheet name, and if the name is valid (no non-case sensitive repetition, etc.) it will create a copy of the "Master Template" sheet, rename it, and add it after the last tab. This works fine in the original sheet, but when I copy and paste the sheet to a new workbook, the macro referenced by the button is still the one on the original sheet, not on the copy itself. It even opens the old sheet when the button is pressed, and runs the macro there. The "Master Template" being copied is from the new workbook, and it is pasted in the new workbook. However, when checking to see if the name is valid, it references the sheet names in the old workbook, as well as the location of the last tab from the old workbook to know where to insert the duplicated sheet.
For example, consider the "New" that workbook has the TOC sheet copied from the "Old" workbook. If "Old" has 3 sheets and "New" has 5, a valid name entry will duplicate the "Master Template" from "New" and paste it after the 3rd sheet of "New". If "Old" has more sheets than "New" then running the macro and giving a valid name will give a sub out of bounds exception. Additionally, if a sheet name is used in "Old" but not in "New" it will be considered invalid.

This, of course, can be fixed by right clicking the button and switching the macro it references to the one located in the "New" sheet, but I would like to avoid making the user go into the backend at all if possible. I think I read something about an excel extension that could fix this problem, but I have no experience with that.

I have pasted the macro below. You can create an excel workbook with a sheet named "TOC" that has a button and a sheet named "Master Template" to be duplicated. ***Make sure you paste the macro within the TOC sheet itself and not a module. Then copy the sheet itself to a new workbook that has a "Master Template" sheet as well. You can experiment with different sheet names and amounts and it might help you visualize what I have explained above.
(I apologize if I explained it badly. I wrote out something nicer, but I left my browser open and the page refreshed and deleted what I had. I did my best to retouch on all my thoughts.)

Thanks for your time. I hope there is a simple solution to this.

Cheers.

Public Sub CopySheet()

Dim templateHidden As Boolean

    Application.ScreenUpdating = False
    templateHidden = False
    
    'Is the Master Template Hidden?
    If Worksheets("Master Template").Visible = False Then
        templateHidden = True
    End If
    
    newName = Application.InputBox("Enter name of New Sheet:", "New Sheet Creation", Type:=2)
    
    'Quit if cancel was pushed
    If newName = "" Or StrPtr(newName) = 0 Or newName = vbNull Or newName = False Then
        Exit Sub
    End If
    
    'If name exists already, keep asking for a new one until you get one that works
    tableArray = Range("C6:C250")
    Do While isInArray(newName, tableArray) Or newName = Me.Name
        newName = Application.InputBox("That name already exists. Please try again with another name:", "New Sheet Creation", Type:=2)
        
        'Quit if Cancel was pushed
        If newName = "" Or StrPtr(newName) = 0 Or newName = vbNull Or newName = False Then
            Exit Sub
        End If
    Loop

    'Create the new sheet and name it accordingly
    
    'If Master Template is hidden, unhide the duplicate
    If templateHidden Then
        Sheets("Master Template").Visible = True
        Sheets("Master Template").Copy after:=Sheets(ThisWorkbook.Sheets.Count)
        Sheets("Master Template").Visible = False
    Else
        Sheets("Master Template").Copy after:=Sheets(ThisWorkbook.Sheets.Count)
    End If
    
        
    ActiveSheet.Name = newName

    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Therein lies the problem. Since you are copying the sheet over to a new workbook, the links are automatically created back to the source workbook. You will need to break the links to solve the problem. I think I would put the code in a standard module rather than a sheet module. Then you could just copy the code from workbook to workbook without copying the entire sheet. You would have to set your button up to run the code from the standard module.

 

[uahmed318]

You would have to set your button up to run the code from the standard module.

Would I be able to link the module somehow such that when the sheet is copy/pasted the module follows it to the next workbook? Ideally the user wouldn't have to go into the backend at all, and changing the macro referenced by a button is much more simple than going into the backend and copying a module.

 

[JLGWhiz]

Would I be able to link the module somehow such that when the sheet is copy/pasted the module follows it to the next workbook? Ideally the user wouldn't have to go into the backend at all, and changing the macro referenced by a button is much more simple than going into the backend and copying a module.

I think you missed the point. When you copy a sheet from one workbook to another, Code and formulas in that sheet are automatically linked to the original workbook. If you do not want those links, you have to remove them by using the Name Manager,

 

[uahmed318]

Yes, I'm aware of this. What I'm wondering is if there is a way to automate changing the references or write the code in a way such that I can copy and paste the sheet cleanly. I read something a while ago about an add in or extension that would let you package a sheet like this so it can be moved around nicely but I'm having trouble finding any reading on it.

 

[JLGWhiz]

That should have been to remove links, go to Data|Edit Links then remove unwanted links.

 

[JLGWhiz]

A few years ago there was plenty of information on the web about how to do things like that. I believe that most of it has been archived now except for what you can find on some of the MVP sites like Chip Pearson's at Pearson Tecnical Services and Barbra Daiglish at Contextures and a couple of others. Like you, I have read it somewhere, but my old brain doesn't hold things like it used to. I have to look up a lot of stuff I used to knoiw off hand.

 

[Wanderer_geologist]

You can eventually take that need the reverse way
create a macro to copy the workbook then remove all extra tab and unecessary data for a blank template
or
create a blank template with the code and copy that template over via macro.

 

[JLGWhiz]

I have a file of personal data that I have to renew annually. I do a SaveAs to create the new year's file, make all the data in the old file values only to kill the formulas and archive the old file. The new file has the vba program intact, so all I do is delete the cells where data is entered manually and any other cells with dependent data that do not have a formula. I also have to manually reset a data series for a chart. But the code does not reference the old workbook and even the chart references remain the same. I do sometimes update the code because as I am deleting the old data I will think of something that I can improve, but not anything related to the old workbook. I keep threatening to write a macro to do the annual swithover, but somehow it just keeps getting put aside. One day I will probably do it.