+ Reply to Thread
Results 1 to 18 of 18

Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so How?

  1. #1
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so How?

    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.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Need macro to change data on tabs when info in F7 of Job Info worksheet changes

    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]

  3. #3
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to change data on tabs when info in F7 of Job Info worksheet changes

    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.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Need macro to change data on tabs when info in F7 of Job Info worksheet changes

    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?

  5. #5
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Need macro to change data on tabs when info in F7 of Job Info worksheet changes

    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!!!!

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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?

  7. #7
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    Right click on the Job Info sheet and select View Code. Paste this code in -
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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!!!!

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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.

  11. #11
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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!!

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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.

  13. #13
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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?

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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?

    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?
    It will make it simpler if you change it to what the other sheets look like.

  15. #15
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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.

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    Try this code
    Please Login or Register  to view this content.
    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.

  17. #17
    Forum Contributor
    Join Date
    07-02-2012
    Location
    Florida
    MS-Off Ver
    Home -Excel 2007, Work 2016
    Posts
    254

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    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

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so H

    Can you please attach your file? The macro worked on the file you uploaded earlier.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1