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.

 

[JoeMo]

Can you post Macro1 so we can see what it is doing? We need to know if its selecting cells on the activesheet (typical of recorded macros) or doing anything else that requires the sheet it acts on to be active.

 

[pc732015]

Hi JoeMo,

Thanks for your response and sorry for the delay. I've attached a sample of the excel macro workbook in https://www.dropbox.com/s/sv8jj2o5sqyt2po/Example to show Macro1.xlsm?dl=0&m= which shows only 5 worksheets. Pls go through and let me know if it's possible to create a Macro to run Macro1 automatically on the 500 worksheets. Thanks.

 

[JoeMo]

Sorry, I don't download from the web.

 

[pc732015]

Hi JoeMo,

I have attached 5 sample worksheets below. Pls let me know if it's possible to create a Macro to run Macro1 automatically on the 500 worksheets. Thanks.




This is Worksheet 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 Worksheet 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 Worksheet 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 Worksheet 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 Worksheet 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​

 

[BrianJN1]

Firstly, what is Macro1 supposed to do?
Row 1 initially is empty but Column A-D are assigned Column Headers.

Now at row 100 I see those same headers atop Pivot tables.

Going down to row 200 I see a copy of the range A1:D10, no pivot.

Secondly, I note that the data in the other 4 sheets is different to the first. How would you anticipate that Macro1 would work with them?

Thirdly, with those two issues clarified, how would you hope to identify 500 sheets? Might it be something like "Equip 001", "Equip 002", "Equip 003", ... "Equip 500"?

 

[pc732015]

Hi Brian,

Macro1 is to allow me to see which Area of the Equipment where PartA is been used most. Pls note that the sample data that I've given here is just a portion of the actual usage. If there are a few worksheets only (each worksheet represent an Equipment), I can click on each worksheet respectively and run Macro1. But due to there are 500 worksheets, it will take me a long time to go to individual worksheet to run Macro1, so that's why I'm asking here whether if there's any possibility creating a Macro to help me. Thanks.

 

[pc732015]

To add on further, the final data I'm interested in starts from Row200 till the last row just before the empty row in each worksheet and I will consolidate these data from each worksheet into a Master worksheet using another Macro which I've already figured out from https://www.mrexcel.com/forum/excel...ata-multiple-worksheets-into-1-worksheet.html post #6 . Thanks.

 

[My Aswer Is This]

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.

 

[BrianJN1]

Hi Brian,

Macro1 is to allow me to see which Area of the Equipment where PartA is been used most. Pls note that the sample data that I've given here is just a portion of the actual usage. If there are a few worksheets only (each worksheet represent an Equipment), I can click on each worksheet respectively and run Macro1. But due to there are 500 worksheets, it will take me a long time to go to individual worksheet to run Macro1, so that's why I'm asking here whether if there's any possibility creating a Macro to help me. Thanks.

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.