Macro to trigger another Macro

[pc732015]

Hi, I have a Macro (Macro1) which is created using the Macro recorder. Macro1 is used to do similar functions in each worksheet. I can actually go to each worksheet and run Macro1 but I have altogether 500 worksheets, which means I have to do 500 times. I'm thinking of creating another Macro to run Macro1 automatically on the 500 worksheets but I'm a newbie and don't know how to cycle through all the 500 worksheets. Can someone help me? Thanks.

 

[pc732015]

Why can you not just post your Macro code here in the forum. We should not be asked to download your code from a link you have provided. Links can always be dangerous.

Hi, I have earlier replied with the worksheets and the Macro code but not sure why it needs to be sent to the admin of this forum for approval and until now, that reply has not been posted yet. I'll send here again and see if gets to the admin again.

Below is the sample data for 5 worksheets. Each worksheet represents an Equipment.
This is EquipmentA

	A	B	C	D
2	EquipmentA	PartA	Area9	1
3	EquipmentA	PartA	Area11	1
4	EquipmentA	PartD	Area9	3
5	EquipmentA	PartF	Area11	1
6	EquipmentA	PartB	Area7	1
7	EquipmentA	PartA	Area2	1
8	EquipmentA	PartA	Area8	3
9	EquipmentA	PartA	Area10	2
10	EquipmentA	PartB	Area6	2
11	EquipmentA	PartA	Area1	1
12	EquipmentA	PartC	Area2	1
13	EquipmentA	PartA	Area7	1
14	EquipmentA	PartD	Area11	1
15	EquipmentA	PartG	Area9	1
16	EquipmentA	PartA	Area8	1
17	EquipmentA	PartB	Area6	2
18	EquipmentA	PartK	Area10	3
19	EquipmentA	PartA	Area10	1
20	EquipmentA	PartA	Area8	2

<tbody>
</tbody>

This is EquipmentB.

	A	B	C	D
2	EquipmentB	PartA	Area9	1
3	EquipmentB	PartA	Area8	2
4	EquipmentB	PartA	Area11	1
5	EquipmentB	PartA	Area10	1
6	EquipmentB	PartD	Area9	3
7	EquipmentB	PartA	Area8	1
8	EquipmentB	PartF	Area11	1
9	EquipmentB	PartB	Area6	2
10	EquipmentB	PartA	Area2	1
11	EquipmentB	PartG	Area9	1
12	EquipmentB	PartA	Area8	3
13	EquipmentB	PartD	Area11	1
14	EquipmentB	PartA	Area10	2
15	EquipmentB	PartA	Area7	1
16	EquipmentB	PartB	Area6	2
17	EquipmentB	PartC	Area2	1
18	EquipmentB	PartA	Area1	1

<tbody>
</tbody>

This is EquipmentC.

	A	B	C	D
2	EquipmentC	PartA	Area1	1
3	EquipmentC	PartB	Area6	2
4	EquipmentC	PartA	Area10	2
5	EquipmentC	PartD	Area11	1
6	EquipmentC	PartG	Area9	1
7	EquipmentC	PartB	Area6	2
8	EquipmentC	PartB	Area7	1
9	EquipmentC	PartA	Area8	3
10	EquipmentC	PartA	Area2	1
11	EquipmentC	PartK	Area10	3
12	EquipmentC	PartA	Area8	1
13	EquipmentC	PartA	Area11	1
14	EquipmentC	PartA	Area9	1

<tbody>
</tbody>

This is EquipmentD.

	A	B	C	D
2	EquipmentD	PartA	Area1	1
3	EquipmentD	PartB	Area6	2
4	EquipmentD	PartA	Area10	2
5	EquipmentD	PartA	Area8	3
6	EquipmentD	PartA	Area2	1
7	EquipmentD	PartB	Area7	1
8	EquipmentD	PartF	Area11	1
9	EquipmentD	PartD	Area9	3
10	EquipmentD	PartA	Area11	1
11	EquipmentD	PartA	Area7	1
12	EquipmentD	PartD	Area11	1
13	EquipmentD	PartG	Area9	1
14	EquipmentD	PartB	Area6	2
15	EquipmentD	PartK	Area10	3
16	EquipmentD	PartA	Area8	1
17	EquipmentD	PartA	Area10	1
18	EquipmentD	PartA	Area8	2

<tbody>
</tbody>

This is EquipmentE.

	A	B	C	D
2	EquipmentE	PartA	Area9	1
3	EquipmentE	PartA	Area11	1
4	EquipmentE	PartD	Area9	3
5	EquipmentE	PartF	Area11	1
6	EquipmentE	PartB	Area7	1
7	EquipmentE	PartA	Area2	1
8	EquipmentE	PartA	Area8	3
9	EquipmentE	PartA	Area10	2
10	EquipmentE	PartB	Area6	2
11	EquipmentE	PartA	Area1	1
12	EquipmentE	PartC	Area2	1
13	EquipmentE	PartA	Area7	1
14	EquipmentE	PartK	Area10	3
15	EquipmentE	PartA	Area10	1
16	EquipmentE	PartA	Area8	2

<tbody>
</tbody>

This is Macro1.

Sub Macro1()
'
' Macro1 Macro
'


'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Equipment No."
Range("B1").Select
ActiveCell.FormulaR1C1 = "Part No."
Range("C1").Select
ActiveCell.FormulaR1C1 = "Area where Part is used"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Qty"
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.Goto Reference:="R100C1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("EquipmentA").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("EquipmentA").Sort.SortFields.Add Key:=Range( _
"B101:B119"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("EquipmentA").Sort
.SetRange Range("A100:D119")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=6
Range("B100").Select
Selection.AutoFilter
ActiveSheet.Range("$A$100:$D$119").AutoFilter Field:=2, Criteria1:="PartA"
Range("A100").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Application.Goto Reference:="R200C1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
End Sub


Pls help me with the code to automate running Macro1 for the 500 worksheets. Thanks.

 

[pc732015]

Fine.
It is possible to run a macro across your 500 sheets (if excel allows that creation). My main consideration was about how those sheets might be named, thus my naming of "Equip 001", etc. Are you happy that Macro1 as it stands meets your requirements? If so, provide a consistent means to name your sheets; if alphabetic I guess the progress is "..z", "..aa", "..ab", etc.

Hi Brian, I'm happy with Macro1 but I need another Macro to help me automate the running of Macro1 for the 500 worksheets. If you could help me on the code, that will be good. As regards to the naming of the equipment in these example, they are just named EquipmentA and so on here for convenience. In fact, my equipment has their own respective models and numbers which I think that the naming convention shouldn't have any effect on the Macro to be written. Thanks.

 

[BrianJN1]

Is the sort of thing which you are seeking then?

I have taken your Sheet EQUIPMENTA and labeled it "First" on the assumption that your data will be input from there.
I've then created 5 sheets, using EQUIPMENTX as the model you have for your example.

The first MACRO creates copies of "First" but renames them as it goes, but then, after the creation of each sheet Macro1 is called before moving to the next sheet.
If this is what you are asking this will take quite some time to create 500 sheets, and I've attempted to speed this up but it takes about 10 secs to perform just what I have offered.

Something about this feels wrong to me, please advise.

Sub make_Sheets()


' Build Macro
'

'
   Dim a As Long
   Dim tag As String
   Dim Prefix As String
   Application.ScreenUpdating = False
 '  Application.Calculation = xlCalculationManual
   Prefix = "EQUIPMENT"
   
   For a = 1 To 5
    tag = Chr(64 + a)

   Sheets("First").Select
    Cells.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
        
    Cells.Select
    ActiveSheet.Paste
   
 
    ActiveSheet.Name = Prefix & tag
     macro1
   Next
   Application.ScreenUpdating = True
 '  Application.Calculation = xlCalculationAutomatic
   End Sub

Sub macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Equipment No."
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Part No."
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Area where Part is used"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Qty"
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.Goto Reference:="R100C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=12
    Application.CutCopyMode = False
    ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "B101:B119"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A100:D119")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B100").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$100:$D$119").AutoFilter Field:=2, Criteria1:="PartA"
    Range("A100").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.Goto Reference:="R200C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=12
End Sub

 

[pc732015]

Is the sort of thing which you are seeking then?

I have taken your Sheet EQUIPMENTA and labeled it "First" on the assumption that your data will be input from there.
I've then created 5 sheets, using EQUIPMENTX as the model you have for your example.

The first MACRO creates copies of "First" but renames them as it goes, but then, after the creation of each sheet Macro1 is called before moving to the next sheet.
If this is what you are asking this will take quite some time to create 500 sheets, and I've attempted to speed this up but it takes about 10 secs to perform just what I have offered.

Something about this feels wrong to me, please advise.

Sub make_Sheets()


' Build Macro
'

'
   Dim a As Long
   Dim tag As String
   Dim Prefix As String
   Application.ScreenUpdating = False
 '  Application.Calculation = xlCalculationManual
   Prefix = "EQUIPMENT"
   
   For a = 1 To 5
    tag = Chr(64 + a)

   Sheets("First").Select
    Cells.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
        
    Cells.Select
    ActiveSheet.Paste
   
 
    ActiveSheet.Name = Prefix & tag
     macro1
   Next
   Application.ScreenUpdating = True
 '  Application.Calculation = xlCalculationAutomatic
   End Sub

Sub macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Equipment No."
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Part No."
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Area where Part is used"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Qty"
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.Goto Reference:="R100C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=12
    Application.CutCopyMode = False
    ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "B101:B119"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A100:D119")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B100").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$100:$D$119").AutoFilter Field:=2, Criteria1:="PartA"
    Range("A100").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.Goto Reference:="R200C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=12
End Sub

Hi Brian,

I don't understand why is there a need to create copies of the existing worksheets again. Actually my equipment names are different and can jump sequence, for eg. I have equipment that starts with ADB, ADC, ADT, ADV, TW, TM (these are from different vendors with different models) etc with 3-digit numbers behind like ADB-001, ADB-004, ADB-006, etc...... So if copies need to be created, there will be total of 1000 worksheets and your script will not work in this case. Sorry for not informing earlier. I prefer my existing worksheets to stay as it is. Thanks. I appreciate your help in this and once again I apologize for not informing earlier.

 

[BrianJN1]

I could not understand what this was supposed to mean:

I'm happy with Macro1 but I need another Macro to help me automate the running of Macro1 for the 500 worksheets.

I saw that your 5 sheets had different content but I couldn't make sense as to how they were to relate to 500 sheets!

I feel that what you are telling me is that you wanted Macro1 to run in such a way that no other Worksheet is compromised when it runs.

This small snippet of code asks you to enter the Worksheet name that you want to rearrange as per Macro1. Upon selecting that name Macro1 takes over.

Is this what you were seeking?

Sub Before_Macro1()
Dim a As String
a = InputBox("Sheetname to order")
Sheets(a).Select
Macro1
End Sub

Or did you want the macro to address each sheet separately in a single run? That was the reason why I asked how you were naming your worksheets.

 

[pc732015]

I could not understand what this was supposed to mean:

I saw that your 5 sheets had different content but I couldn't make sense as to how they were to relate to 500 sheets!

I feel that what you are telling me is that you wanted Macro1 to run in such a way that no other Worksheet is compromised when it runs.

This small snippet of code asks you to enter the Worksheet name that you want to rearrange as per Macro1. Upon selecting that name Macro1 takes over.

Is this what you were seeking?

Sub Before_Macro1()
Dim a As String
a = InputBox("Sheetname to order")
Sheets(a).Select
Macro1
End Sub

Or did you want the macro to address each sheet separately in a single run? That was the reason why I asked how you were naming your worksheets.

These 5 worksheets are just 5 samples of the 500 worksheets (each worksheet stands for one equipment) that I have and for convenience sake, I have just named them as Equipment A to E which in actual fact they are not named thus. My equipment names are different and can jump sequence, for eg. I have equipment that starts with ADB, ADC, ADT, ADV, TW, TM (these are from different vendors with different models) etc with 3-digit numbers behind like ADB-001, ADB-004, ADB-006, etc.....

What I mean by I'm happy with Macro1 is after recording the macro using EquipmentA worksheet, I clicked on EquipmentB worksheet and run Macro1 and it's able to perform what I want. Then I also clicked on worksheets EquipmentC to EquipmentE, one worksheet by one worksheet respectively and run Macro1 and happy to achieve what I want. But my problem here is I have 500 worksheets, this means that I have to click on the other 495 worksheets one by one to run Macro1. This is going to take up a lot of time. So I'm asking help here whether I can have a macro done so that with when I run the macro, all the 500 worksheets will have Macro1 run without me having to click the 500 worksheets individually. I hope you understand what I mean. Thanks.

 

[BrianJN1]

So what you want then is what I queried above:

Or did you want the macro to address each sheet separately in a single run? That was the reason why I asked how you were naming your worksheets.

Now, while I was looking for some consistency of labeling of Tags, I think there is a way to modify my macro to begin at your first worksheet and then count its way across the sheets. I'll review and see what I can do.

 

[BrianJN1]

Ok. Try this:

Sub Before_Macro1()
   Dim x As Integer
     For x = 1 To ThisWorkbook.Worksheets.Count
     Worksheets(x).Select
        Macro1
    Next
End Sub

I do suspect however that if you are going to run this over 500 worksheets then this version may be more appropriate to reduce runtime:

Sub Before_Macro1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculation.Manual
   Dim x As Integer
     For x = 1 To ThisWorkbook.Worksheets.Count
     Worksheets(x).Select
        Macro1
    Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculation.Automatic
End Sub

May I suggest that you run this on a copy of the workbook as a test before applying it committing it to your original.

 

[pc732015]

Ok. Try this:

Sub Before_Macro1()
   Dim x As Integer
     For x = 1 To ThisWorkbook.Worksheets.Count
     Worksheets(x).Select
        Macro1
    Next
End Sub

This code works well and achieve what I want. Thanks very much, Brian.

I do suspect however that if you are going to run this over 500 worksheets then this version may be more appropriate to reduce runtime:

Sub Before_Macro1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculation.Manual
   Dim x As Integer
     For x = 1 To ThisWorkbook.Worksheets.Count
     Worksheets(x).Select
        Macro1
    Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculation.Automatic
End Sub

May I suggest that you run this on a copy of the workbook as a test before applying it committing it to your original.

This code doesn't work and has this following:

Compile error:
Method or data member not found


But it's ok as the first code already works.


Thanks Brian.