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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Macro to trigger another Macro
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
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]EquipmentA[/TD]
[TD]PartD[/TD]
[TD]Area9[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]EquipmentA[/TD]
[TD]PartF[/TD]
[TD]Area11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EquipmentA[/TD]
[TD]PartB[/TD]
[TD]Area7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]EquipmentA[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]EquipmentA[/TD]
[TD]PartC[/TD]
[TD]Area2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]EquipmentA[/TD]
[TD]PartD[/TD]
[TD]Area11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]EquipmentA[/TD]
[TD]PartG[/TD]
[TD]Area9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]EquipmentA[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]EquipmentA[/TD]
[TD]PartK[/TD]
[TD]Area10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]EquipmentA[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
This is EquipmentB.
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EquipmentB[/TD]
[TD]PartD[/TD]
[TD]Area9[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EquipmentB[/TD]
[TD]PartF[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]EquipmentB[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]EquipmentB[/TD]
[TD]PartG[/TD]
[TD]Area9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EquipmentB[/TD]
[TD]PartD[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]EquipmentB[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]EquipmentB[/TD]
[TD]PartC[/TD]
[TD]Area2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]EquipmentB[/TD]
[TD]PartA[/TD]
[TD]Area1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
This is EquipmentC.
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]EquipmentC[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]EquipmentC[/TD]
[TD]PartD[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EquipmentC[/TD]
[TD]PartG[/TD]
[TD]Area9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EquipmentC[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EquipmentC[/TD]
[TD]PartB[/TD]
[TD]Area7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]EquipmentC[/TD]
[TD]PartK[/TD]
[TD]Area10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]EquipmentC[/TD]
[TD]PartA[/TD]
[TD]Area9[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
This is EquipmentD.
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]EquipmentD[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EquipmentD[/TD]
[TD]PartB[/TD]
[TD]Area7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EquipmentD[/TD]
[TD]PartF[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]EquipmentD[/TD]
[TD]PartD[/TD]
[TD]Area9[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]EquipmentD[/TD]
[TD]PartD[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EquipmentD[/TD]
[TD]PartG[/TD]
[TD]Area9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]EquipmentD[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]EquipmentD[/TD]
[TD]PartK[/TD]
[TD]Area10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]EquipmentD[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
This is EquipmentE.
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]EquipmentE[/TD]
[TD]PartD[/TD]
[TD]Area9[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]EquipmentE[/TD]
[TD]PartF[/TD]
[TD]Area11[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]EquipmentE[/TD]
[TD]PartB[/TD]
[TD]Area7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]EquipmentE[/TD]
[TD]PartB[/TD]
[TD]Area6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]EquipmentE[/TD]
[TD]PartC[/TD]
[TD]Area2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]EquipmentE[/TD]
[TD]PartK[/TD]
[TD]Area10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]EquipmentE[/TD]
[TD]PartA[/TD]
[TD]Area8[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
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.
Upvote
0
Excel Facts
Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
Upvote
0
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.
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.
Code:
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
Upvote
0
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.
Code: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.
Upvote
0
I could not understand what this was supposed to mean:
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?
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.
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'm happy with Macro1 but I need another Macro to help me automate the running of Macro1 for the 500 worksheets.
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?
Code:
Sub Before_Macro1()
Dim a As String
a = InputBox("Sheetname to order")
Sheets(a).Select
Macro1
End SubOr 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.
Last edited:
Upvote
0
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?
Code: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.
Upvote
0
So what you want then is what I queried above:
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.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.
Upvote
0
Ok. Try this:
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:
May I suggest that you run this on a copy of the workbook as a test before applying it committing it to your original.
Code:
Sub Before_Macro1()
Dim x As Integer
For x = 1 To ThisWorkbook.Worksheets.Count
Worksheets(x).Select
Macro1
Next
End SubI do suspect however that if you are going to run this over 500 worksheets then this version may be more appropriate to reduce runtime:
Code:
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 SubMay I suggest that you run this on a copy of the workbook as a test before applying it committing it to your original.
Upvote
0
Ok. Try this:
Code: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:
Code: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.
Upvote
0
Similar threads
- Question
- Replies
- 1
- Views
- 380
- Replies
- 6
- Views
- 156
- Replies
- 12
- Views
- 727
