Calling a series of macros (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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

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


H.T.H.

Reply | Quote

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?

Reply | Quote

Hello David

• 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,
• 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.

Reply | Quote

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)……??..??

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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)?

Reply | Quote

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.

Sub DoMany4DJM()
Dim Wkbk As Workbook

'Selects the containing workbooks one at a time
For Each Wkbk In Workbooks
If UCase(Wkbk.Name) "PERSONAL.XLS" _
And UCase(Wkbk.Name) "TIMEDEVOVERTIME.XLS" _
And UCase(Wkbk.Name) "TIMEDEVMONTHEND.XLS" Then

'Activates the Containing workbook
Wkbk.Activate

'Runs the macro within the Containing workbook
Application.Run Wkbk.Name & "!DoBoth"

'Saves and closes the Containing workbook
Wkbk.Close True

End If
Next Wkbk
End Sub


Reply | Quote

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…..

Reply | Quote

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)?

Reply | Quote

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. Delete the command Call Save_and_Close from the DoBoth procedure in each of the individual timesheet workbooks; or
2. 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.

Reply | Quote

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.

Reply | Quote

Thank you, Don….I re-read your code and the post by GoCush and it all made sense to me….I have deleted all the code from the individual timesheets; installed the code you gave me into the Overtime wkbk and did some test runs and so far it performs perfectly….amazing….thank you again — if I encounter some glitches, I will come back to this post….now, all I have to do is return to my other related post ((calculating cumulative time) and everything should be good….

Reply | Quote

You assumed correctly.

Reply | Quote