Combine macros

[kctony]

I have 3 macros that are sequentially connected. I am trying to merge them all into one macro. I am missing something but I can't figure out what so I thought i'd try here as a cheat.
My first macro collects a month's worth of data. My second macro consolidates taht data and sends it to a new sheet and creats a Pivot Table. My 3rd macro creats another pivot table and titles the worksheets however the 'UserColums' in the second macro nevder works even thlough it is still in the code..
I want all of this to happen in one macro so that 1 click does it all.
I know it's simple but it just escapes me at the moment.
Thank you for your help.
Tony

 

[Truiz]

Re: How to combine macros

Try this

Sub MightyMacro ()
Call Macro1
Call Macro2
Call Macro3
End Sub

Change to the name of your Macros and in the order they need to be ran

 

[kctony]

Re: How to combine macros

Try this

Sub MightyMacro ()
Call Macro1
Call Macro2
Call Macro3
End Sub

Change to the name of your Macros and in the order they need to be ran

Thank you for the help.
The issue I am having now is that my 2nd macro runs before the 1st macro is complete causing an error . The 2nd macro need information from the 1st macro to complete so I get an error telling me the information is not there.
It works fine when I run them separately but it crashes on macro 2 before macro 1 ihas finished.
Thanks for your help

Application.Run "PERSONAL.XLSB!Pyx_consolidation.Pyx_consolidation"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R45966C82", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6

Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")

 

[Truiz]

Re: How to combine macros

This is odd... let's try another method

Sub RunAll()
Application.Run ("Macro1")
Application.Run ("Macro2")
Application.Run ("Macro3")
End Sub

 

[kctony]

Re: How to combine macros

Hi,
That didn't seem to work but I did get some new error messages including "ambiguous name"... That was new.
I think all I need ACTUALLY IS A TIME DELAY.
i tried using one varying the delay between 10 and 20 seconds,( the data is variable so it's just a guess as to how long it takes). It would be nice to be able to have Macro2 initiate upon completion of Macro1.
I usually have 28-31 sheets of data from Macro1. I know I need to set-up a sheet count but I'm a little fuzzy on how to accomplish that. Would that be just a count or a Do Loop?
Thanx for all your help

 

[Truiz]

Re: How to combine macros

It would be nice to be able to have Macro2 initiate upon completion of Macro1.

That is the usual and expected result there is no reason for them to run simultaneously but lets try something dirty just to make sure each individual macro is not the issue itself.

let's try this

[COLOR=#333333]Sub RunAll()
[/COLOR]Application.Run ("Macro1")
Application.Run ("Macro2")
Application.Run ("Macro3") [COLOR=#333333]End Sub[/COLOR]

BUT.... at the end of each macro lets add a message box (That way we know the macro ran)

so we would have

Sub Macro1 ()
'some code
MsgBox ("Macro1 Completed")
End Sub

Sub Macro2 ()
'some code
MsgBox ("Macro2 Completed")
End Sub

Sub Macro3 ()
'some code
MsgBox ("Macro3 Completed")
End Sub

Give that a try this way when Macro 1 is completed we will see the message box once you click ok Macro 2 should start and so on...

 

[kctony]

Re: How to combine macros

I just wanted to let you know how I resolved the problem and just exactly what the problem was incase anyone has been following this and has tangled with a similar problem.
Initially I stated that Macro 2 would initiate before Macro 1 was finish and crash because there was not data yet because Macro 1 hadn’t finished.
Attempts to fix that also failed.
I added a time delay before the start of Macro 2 but that didn’t seem to do the trick either but it did delay the start of Macro 2 beautifully. But when Macro 2 ran it crashed because there was no data from Macro 1.
I realized the problem was with the names of the Macros.
Macro 1: PyxArch
Macro 2: CompleteAudit
Macro 3: PyxisAuditPart3
Apparently, the Macros run alphabetically… Who knew! It’s probably VB 101 but I never caught that.
Macro 2, being the first in line alphabetically, ran first and crashed because the data from Macro 1 wasn’t available because it hadn’t run yet.
When I changed the names to AuditPart1, AuditPart2, and AuditPart3 it ran beautifully and the time delay was not necessary.
At this point, I’m not even sure if the CALL process is necessary… but hey! If it ain’t broke, don’t fix it!
Thanks for all you help