Worksheets which currently show as Wk 1 should show whatever is entered into F7 of the Job Info tab. Those worksheets which currently show Wk 2 need to be whatever is entered into F7 of the Job Info tab, plus 1, those worksheets which currently show Wk 3 need to be whatever is entered into F7 of the Job Info tab, + 2 and those worksheets which currently show Wk 4 need to be whatever is entered into F7 of the Job Info tab, plus 3.
Master wout macros - logos 7.xlsmWorkbook
Last edited by ILoveStMartin; 11-11-2012 at 10:53 PM.
Do you want the sheetname to be changed as soon as the new sheet is inserted? Or do you want to run the macro on the whole workbook once?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Not sure of your question. There is no sheet inserted.
When F7 is changed the tabs could change, or they could change when all the data is entered into the Job Info worksheet & saved.
Ok, i thought you wanted the name of the sheet to change as soon as a new sheet is inserted.
So all the sheets will have generic names like Sheet1, Sheet2, etc and once you click on the button, you want the sheet names to be changed?
An example of what I am hoping for -
Looking at the workbook you will see a Payroll Wk 1, a Payroll Wk 2, a Payroll Wk 3 and a Payroll Wk 4.
When an entry is made in F7 of the Job Info worksheet, I need what is now Payroll Wk 1 to change to the number entered in F7, i.e. it would change to Payroll Wk 5 if 5 is entered in F7. Payroll Wk 2 to change to 1 greater than what was entered in F7, i.e. it would change to Payroll Wk 6 if 5 is entered into F7. And so on, with Payroll Wk 3 changing to 2 greater than what was entered in F7 and Payroll Wk 4 to change to 3 greater than what was entered in F7.
The changes would occur to all Wk 1, Wk 2, Wk 3 and Wk 4 sheets - with only the number changing and the words staying as is
Thanks for working on this!!!!
Is it ok if the sheets are interchanged?
For e.g. If you already have sheets like Payroll wk 1, 2, 3 & 4 and you put in 2 in F7, then when you start renaming it will error out.
So i can ask the macro to check if payroll wk 2 already exists, then only create the others. Will this work?
This is a 4 week cycle form. At the end of week 4 they would start a fresh form and enter 5 in F7, At the end of that 4 weeks, they would start a fresh form and enter 9 in F7, so there would never be a 2 or any other number that had appeared there before. Numbers would always be 4 greater than the number in F7 of the just completed workbook.
Right click on the Job Info sheet and select View Code. Paste this code in -
Please Login or Register to view this content.
Great!
Now all I need is info on how to make it run.
What I have done so far
opened the workbook
did a alt+F11
Clicked on the workbook needing this macro and then did a right click on it's "thisworkbook"
clicked view code and pasted what you sent.
Question - What is the trigger that makes it run?
Also - so I learn / know what is happening -
How is it tied to the Job Info sheet - which is the one and only worksheet where the F7 cell is of importance to this macro?
Could it be set up so it is triggered by an option button?
Thank You!!!!
You should not put the code in thisworkbook. Instead, right click on the Job Info sheet and select View Code. Paste the code in. Once you do this, the macro will only be triggered when the cell F7 in the Job Info sheet is changed.
FANTASTIC - FANTASTIC - FANTASTIC
I had searched this forum for a macro which would make a change to tabs but found none, so this one of a kind may be of big help to others. If it is of help to you, please be sure to give arlu1201 the accolades she deserves!!
Thanks IloveStMartin.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
I was so excited to see your macro working - WOW!!
I then tried to add code so the same changes happened to the Equipment Wk 1, 2, 3 & 4 sheets; the Chemicals & Materials Wk 1, 2, 3, & 4 sheets, the Expenses Wk 1, 2, 3 & 4 sheets - but none worked, so I need your help on them. While I tried on those 3 groups of sheets, I had no idea on how the macro would read that would also adjust the Wk 1 Billing, Wk 2, Wk 3 & Wk 4 Billing sheets so I need help on them too.
HELP & THANKS! (Would it help if I changed the name of the billing sheets so the number was at the end and not in the middle as it currently is?
Will the week numbers put in Job info affect your other sheets like Equipment, Chemicals, etc or will there be a separate place for the week numbers to be input for these sheets?
Also, when the Payroll week sheets are changed, do you want the other sheets to be changed at the same time?
It will make it simpler if you change it to what the other sheets look like.Would it help if I changed the name of the billing sheets so the number was at the end and not in the middle as it currently is?
Will the week numbers put in Job info affect your other sheets like Equipment, Chemicals, etc or will there be a separate place for the week numbers to be input for these sheets? -
The single F7 entry should generate a change to all worksheets.
Also, when the Payroll week sheets are changed, do you want the other sheets to be changed at the same time?
Yes!!
It will make it simpler if you change it to what the other sheets look like.
I have several macros that run using those Wk * Billing names. Know I can change the names to the sheet number, but have not had the time to do it - too many balls in the air.
If you do the macro so it would change the number in the Billing sheets if I changed them, I will change them to Billing Wk 1, Billing Wk 2, Billing Wk 3 and Billing Wk 4. (Knowing that if you set up the macro according to those 4 names the macro will not work until I change the names - that will be the motivator to make me find the time to get them changed!!)
Thanks!!
Last edited by ILoveStMartin; 11-27-2012 at 11:44 PM.
Try this code
You have to ensure that you change the Billing week sheet numbers. Also, the sheets containing the same name should be together otherwise it wont work.Please Login or Register to view this content.
I do not know what is happening. Not only did this not work, the first one you sent which worked (just changed the payroll sheet - but it changed them) no longer works. But all button triggered macros seem to work.
For this latest, I do have the billing weeks changed to Billing Wk 1 etc. (copied and pasted from the worksheet tab) and each of the other sheets grouped together and in wk secquence.
Any suggestions on the possible cause of the problem?
Thanks
Bill
Can you please attach your file? The macro worked on the file you uploaded earlier.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
More Information
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks