Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Calling a series of macros (Excel 2003)

    Good morning, all....and in particular, Hans -- in December, you helped me with some VBA code that would call 3 or 4 macros in sequence. I am doing the following: I have anywhere from 10-15 workbooks (called "??timsheets") open at once; I also have open 2 workbooks called Overtime and MonthEnd....presently, each timesheet (and they all have different names b/c they come to me from different people) has a macro that will make a SUMMARY sheet the active sheet; copy data from the SUMMARY and paste that data (values only) into the Overtime workbook and into the MonthEnd workbook; the active ??timesheet closes. I then manually move to another "!!timesheet" and call the same series of macros. It looks like this:

    Sub DoBoth()
    '
    ' DoBoth Macro
    ' Macro recorded 12/8/2007 by David J. McNab
    '
    ' Keyboard Shortcut: Ctrl+C

    Call Go_to_Summary
    Call Autoadjust_Summary
    Call Copy_Paste_Month_End
    Call Copy_Paste_Overtime
    Call Save_and_Close

    End Sub

    ....what I am looking for is some code that will not only Save_and_Close the timesheet that was just copied and pasted (into the Overtime and the MOnthEnd workbooks) but will automatically move to the next timesheet (and I can then call the sequence of macros for that timesheet)...in other words, some code that will alleviate me from having to move manually to each timesheet......during all of this the names of the Overtime and the MonthEnd workbooks remain constant, so maybe something that "looks for" any other open .xls file ??..??....not sure, but thought that you would know....I hope I have given enough info, b/c I don't want to make my post too big.....the original post from me was 681244.....Thank you.

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    The following should satisfy your needs, but ensure there are no workbooks open that you do not want processed.

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>
    <font color=blue>Sub</font color=blue> DoMany4DJM()
    <font color=blue>Dim</font color=blue> Wkbk <font color=blue>As</font color=blue> Workbook
    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> Wkbk <font color=blue>In</font color=blue> Workbooks
    <font color=blue>If</font color=blue> UCase(Wkbk.Name) <> "PERSONAL.XLS" _
    And UCase(Wkbk.Name) <> "OVERTIME.XLS" _
    And UCase(Wkbk.Name) <> "MONTHEND.XLS" <font color=blue>Then</font color=blue>
    Application.Run Wkbk.Name & "!DoBoth"
    Wkbk.Close <font color=blue>True</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Next</font color=blue> Wkbk
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    H.T.H.
    Regards
    Don

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Hi Don....I tried your code but with a couple of changes (I removed the UCASE b/c the wkbk names are not in upper case) and I removed the words "OPTION EXPLICIT" (b/c I got a compile error when I ran it)....I copied your code (as amended) into the module where all the existing macros are stored....everything worked as before (ie: it did the copy&paste, closed the workbook that had been copied from) but then stayed with the Overtime workbook as the active one...it did not move to another timesheet....did the changes I made to your code prevent it from working properly?

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Hello David
    <UL><LI> Option Explicit Although this statement is not mandatory it should appear in the Declaration section of each module. But only once, and only in the Declaration section. It will check to ensure all variables are declared,
    <LI> UCase The UCase statement made the filename comparison case insensitive. If you removed it from the left hand side of the statement, you needed to ensure that the right hand side matched the filenames exactly.[/list]Other than this I would want to see the code which you ended up with.

    This should have worked as delivered if placed in a module by itself.
    Regards
    Don

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Hi Don....here is what I am now using:

    Option Explicit
    Sub DoMany4DJM()
    Dim Wkbk As Workbook
    For Each Wkbk In Workbooks
    If UCase(Wkbk.Name) <> "Personal.XLS" _
    And UCase(Wkbk.Name) <> "TimeDevOvertime.XLS" _
    And UCase(Wkbk.Name) <> "TimeDevMonthEnd.XLS" Then
    Application.Run Wkbk.Name & "!DoBoth"
    Wkbk.Close True
    End If
    Next Wkbk
    End Sub

    ...these are the real names for the Overtime and Month end workbooks.....I have installed your code in a separate module in each Timesheet (not in the Overtime and MonthEnd wkbks).....it runs the 'doBoth' part (the copy&paste part) and closes the timesheet but it remains with the Overtime wkbk as the active wrkbk, instead of making another open timesheet active and allowing me to immediately run the macro (using Shift-CTL-c)......??..??

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    First
    copy the module to either the Overtime or Month End workbook

    Then
    Remove the code from the individual timesheets

    Finally Change
    From:
    If UCase(Wkbk.Name) <> "Personal.XLS" _
    And UCase(Wkbk.Name) <> "TimeDevOvertime.XLS" _
    And UCase(Wkbk.Name) <> "TimeDevMonthEnd.XLS" Then

    To:
    If UCase(Wkbk.Name) <> "PERSONAL.XLS" _
    And UCase(Wkbk.Name) <> "TIMEDEVOVERTIME.XLS" _
    And UCase(Wkbk.Name) <> "TIMEDEVMONTHEND.XLS" Then
    Regards
    Don

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Don -- I did what you suggested; when you say "Remove the code from the individual timesheets" I assume you mean remove the code you gave me that I put into the timesheets.......I still have the original copy&paste code in each timesheet...and so it continues to do the copy&paste, but remains with the Overtime wkbk as the active one (showing the 'pasted' data, shaded), instead of making one of the still-open timesheets the active wkbk...did I misunderstand your instructions?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Hi David

    I take it that the DoBoth macro requires that the containing workbook be active at the start. Insert the following line of code between the If statement and the Application.Run statement.

    Wkbk.Activate
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    You assumed correctly.
    Regards
    Don

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Yes...the code that is in the timesheet(s) requires that the Monthend and the Overtime wkbk be open; I then open 10-12 timesheets; I go to one of the t/sheets and hit SHFT-CTL-c and the copy&paste occurs....the Overtime wkbk (the second of the containing wkbks) remains active, so I have to manually move to another timesheet and run the macro again...does this last piece of code make a timesheet active, following the copy&paste..?..b/c putting it where you suggest makes me think that it is intended to 'actviate' the containing wkbks (which,in fact, are already active)?

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    I believe that the procedure as you have it installed in your Overtime workbook emulates exactly what you were originally doing manually. I have commented the code below to clarify.
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Sub</font color=blue> DoMany4DJM()
    <font color=blue>Dim</font color=blue> Wkbk <font color=blue>As</font color=blue> Workbook

    <font color=448800>'Selects the containing workbooks one at a time</font color=448800>
    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> Wkbk <font color=blue>In</font color=blue> Workbooks
    <font color=blue>If</font color=blue> UCase(Wkbk.Name) <> "PERSONAL.XLS" _
    And UCase(Wkbk.Name) <> "TIMEDEVOVERTIME.XLS" _
    And UCase(Wkbk.Name) <> "TIMEDEVMONTHEND.XLS" <font color=blue>Then</font color=blue>

    <font color=448800>'Activates the Containing workbook</font color=448800>
    Wkbk.Activate

    <font color=448800>'Runs the macro within the Containing workbook</font color=448800>
    Application.Run Wkbk.Name & "!DoBoth"

    <font color=448800>'Saves and closes the Containing workbook</font color=448800>
    Wkbk.Close <font color=blue>True</font color=blue>

    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Next</font color=blue> Wkbk
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Regards
    Don

  12. #12
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Sorry if I am confusing things, but presently, the only thing I have to do manually is open the MonthEnd and the Overtime workbooks, along with 10-12 Timesheet workbooks. I also manually select an open Timesheet and run the macros in it that copy data from that Timesheet (SUMMARY page) and paste the data into the MonthEnd workbook and then into the Overtime workbook....when those steps are done, the Overtime workbook (which gets 'pasted into' last) shows as the active workbook.

    I am attaching a txt document which shows the code for these steps.....I need to add to this (either with a macro in a Timesheet, or, in the MonthEnd or Overtime workbook) that will make one of the open Timesheet workbooks the active one (instead of the Overtime workbook)....that way, with a Timesheet active, I can immediately run the copy&paste macro.....
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    Follow-up...if I had a macro in each timesheet that 'made it the active timesheet"could I combine this with the 'DoBoth" macros and use this to draw a timesheet to the front (ie: make it active)?

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling a series of macros (Excel 2003)

    David

    Currently your process is failing because the macro in the first individual timesheet is abruptly terminated by the Save_and_Close procedure without passing control back to the DoMany4DJM procedure.

    There are two ways to resolve this:
    1. <LI>Delete the command Call Save_and_Close from the DoBoth procedure in each of the individual timesheet workbooks; or
      <LI>Replace the existing module containing the DoMany4DJM procedure with the attached module. After opening the dozen or so files, run the DoMany4DJM procedure which you will have in either the TimeDevOvertime or TimeDevMonthEnd workbook but not both. I see no value in maintaining code in each of the timesheets when it need only reside in one workbook. This is based on the assumption that the code currently in the individual timesheets is identical.
    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  15. #15
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling a series of macros (Excel 2003)

    DJM,

    As I understand your original post, you have open:
    1. Overtime
    2. Monthend
    3 Several individual Timesheets -each containing a Do..... that copies data to a Summary sheet.

    You asked for code that would automate the process of activating the Timesheets (rather than the Overtime or MonthEnd wbks ) so you could just keep running the individual Do... macros in each TimeSheet.

    Don's code takes your request a step further: If you put his code into a module in the OVERTIME wbk and run it, it will automatically copy the data from ALL Timesheets (one at a time) into the Summary and close each timesheet before moving on to the next Timesheet. Where I think you are getting hung up is that you are expecting to see each Timesheet before it closes, thinking that you will manually have to run the Do... code for each Timesheet. In other words Don's code processes all timesheets and closes them leaving only the Overtime and MonthEnd wbks open.

    Perhaps what you wanted was to inspect each Timesheet prior to processing it. If that is the goal, then Don's code needs to be modified.

    Hope this helps.

Page 1 of 2 12 LastLast

Posting Permissions

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