Duplicate a Pair of Dependent Sheets And Keep Macros Working

[ryankeane11]

Hello all,

I have a pricing worksheet I have built that works exactly the way we want it to. The first sheet "Job Cost Totals" is our primary sheet where our pricing is completed and our totals are shown. The second sheet "Service Estimate" helps calculate a number of things that add into the "Job Cost Totals" sheet. (For example, putting in prices and quantities in "Service Estimate" B29:C39 and clicking the 'Quick Transfer to Job Cost' button at the top of the sheet will take those numbers and bring them to the "Job Cost Totals" sheet.

These two sheets are built to work together. We also often create different options for pricing (which would require these sheets to be duplicated). While I know we could just create multiple workbooks, there is a desire to be able to have all of our options in this one workbook. The problem I am facing is that when duplicating the sheets, because of how the code is written to go back and forth between these specific sheets, I don't know how to make all the buttons and macros update for the duplicated sheets.

EX. Right now my sheets are hardcoded as JobCost and ServiceEstimate. Multiple parts of the VBA transfer information between these two sheets. When creating a duplicate sheet, the hardcode changes to JobCost1 for the new sheet, but all the VBA for the buttons are still linked to JobCost (original sheet).

There is currently a button on the "Job Cost Totals" sheet at the top right called 'Create New Option Sheet'. What I would like to be able to do is have our team be able to click that button, have a duplicate of "Job Cost Totals" created and a duplicate of "Service Estimate" created, then have all of the macros involved work between those two duplicated sheets separately from the original two. Additionally, then be able to create multiple sets of duplicates that all work in their pairing of JobCost/ServiceEstimate without affecting the others or having to manually update VBA. This is a sheet I am building for people who do not know excel well, so it's important for me to do the most backend work where they can just click a button and have all of this happen automatically.

Example of Final Product: "Job Cost Totals" and "Service Estimate" - "Option #1" and "Estimate Option 1" - "Option #2" and "Estimate Option 2". Each of those pairs of sheets having the macros, buttons, and all coding work between them, updated and ready to go automatically when the 'Create Option Sheet' button is pushed.

A few things I've thought of:
- I wondered about using "ActiveSheet" instead of naming the sheets in the code, but I wouldn't know how to make that work when two specific sheets need to work together.
- Ideally, our team would want the ability to be able to change the tabbed sheet names (not the hardcode) without it affecting the VBA. If this is not possible, then protecting the sheet names from being changed would be an option.
- I thought about creating subs with the potential copied sheet names ahead of time, even if not in use, but then I don't know how to re-assign the buttons in the duplicated sheets to the new subs automatically.

I've attached the workbook here. Let me know if you have any questions - would love to solve this conundrum!

[ryankeane11]

Happy to answer any questions that might help or rephrase if the above is confusing.

On a purely simple level, looking to find out how to duplicate pages with page-specific macros, and have those macros duplicate and work for the new pages. Let me know if there's anything else I can to help clarify!

[cubangt]

will the process of creating the duplicates, include both sheets? Meaning will you always create Job Cost and Service Estimate everytime you click the button?

If you will, then at creation make each pair of sheets match in the naming and since you control the name during that copy process, you can handle that naming convention format so that you can insure that each call to the macro is based on the sheets you are working with..

if you setup a variable for the sheetname then you can use that variable instead of the hardcoded sheet name...
Then when you run the macro, check the name of the active sheet, then use that name and pass it to your routine to run against that sheet pair.

[ryankeane11]

Okay, I'm picking up what you're putting down for the process. Yes it will be a duplicate of both sheets as a pair every time. Can you help explain two things then:

1.) What kind of code would I use to change the hardcode name on the copies? This code I use below creates a copy of just the first sheet, not the second, and changes the actual sheet title and not the hardcode. So how would I decree what the hardcode would be and make a copy of both in this code? (Ideally, I would like the sheet names to be editable, as sometimes we change them based on a certain vendor, if that can be done by changing the hardcode instead)

Please Login or Register  to view this content.

2.) Right now the macros are using 'JobCost' as the hardcode for Sheet1 and 'ServiceEstimate' as hardcode for Sheet2. Right now I have multiple macros that explicitly work between these two sheets as they are directly called via hardcode (such as this one below which is crucial for combining elements from the ServiceEstimate and transfering the information over to the JobCost):

Please Login or Register  to view this content.

When I make a copy of these two sheets, how can I tell the macro buttons on the NEW sheets to update so they are not affecting the hardcoded JobCost and ServiceEstimate sheets, but instead the new names of the copied sheets?

[cubangt]

#1 this portion may prevent it from working, i thought it was only 2 tabs/sheets that were created.. but lets see
What if i were to enter the number 5, what does that current generate as it is now? as long as each "set" is named in a way that can link them all together, i believe it can be accomplished.

#2 if the sheets can be created in unique pairs/sets then once you click to run the macro, you can use the "activesheet" to determine which pair/set you are working with and use that naming convention as the sheet names instead of the hardcoded values.

Depending on how you want to handle the names for each new set, those numbers can be extracted from the sheet name and used to indicate which sheets work together.

Example
Current master set
JobCost
ServiceEstimate

you chose to create a new set
New set
JobCost1
ServiceEstimate1

with a function like this, the number portion can be extracted from the name

Please Login or Register  to view this content.

then anywhere that the sheet name needs to be used, it can be appended with the number it returns..

And the function would also need to be used in the creation of the new sheets so that it knows what the next seq number is available to use in the name...

[ryankeane11]

For the sake of pairing it down so maybe I can get a more rudimentary view on it and then repurpose for more complication after...

Attached is a test workbook with two sheets, Sheet 1 and Sheet 2 as sheet names, Sheet1 and Sheet2 as hardcodes.

I have a button on each sheet that will pull data from the other sheet. If you hit Button 1 on Sheet1, it will get the value of a cell on Sheet2. Button 2 on Sheet2 will do the same from Sheet1.
If I go to create a duplicate of the two sheets, neither button will work.


What I would like to do is create a duplicate pair of Sheet1 and Sheet2, with their macros and buttons, and have the Buttons on the new duplicate sheets have the macro updated to work with the new sheets without having to manually go into the code and change the hardcodes. The purpose of this is for me to build it out and send it to our team (who does not know VBA or coding) and they can just push simple buttons that do everything for them without asking me to manually change the things when they need it.

If I create option sheets via a button, I understand I can use that button to auto-change the sheet name or hardcode, but how would I also have those duplicated buttons update to the respective new sheet names/hardcodes without doing it manually?

I know I could use something like ActiveSheet instead for a macro button...but then how would I use ActiveSheet when two separate sheets that would both be duplicated are called in the code?


Is it possible to do what I'm asking? I guess that's step one, LOL.

Code in the workbook:

Please Login or Register  to view this content.

[ryankeane11]

I'm wondering as we're going through this if maybe the root of what is hanging me up, moreso than the naming of sheets, is how to get the macro buttons to update when a duplicate sheet is made.

Unless there's a way to use ActiveSheet in some version or fashion that can reference two separate sheets. Is there an Offset function for ActiveSheet at all? Like can I have code that says:

Please Login or Register  to view this content.

Or something along those lines maybe?

And if not, then when a sheet with macro buttons is duplicated, how can I get the buttons on the duplicate sheet to have the coding updated for the new sheet name automatically?

[cubangt]

Wont the buttons be part of the copy to the new sheets? Or is the macro be run from the developer macro screen?
If the button is part of the sheet that is copied, then keeping the macro name general, the rest can be handled within the macro.. thats where the active sheet and such comes into play.

So the suggestions im stating is to keep one set of code (macros) that take sheet names as parameters so that it can run against any of the sheets..
Since the macro is going to perform the same task, regardless of sheet, all that needs to happen is to provide it the sheet it needs to run against.