VBA

[Bilaal xaka]

Hello everyone, I need your help please.
(1) I have 2 VBA which I was applying in all sheets in a workbook, but which now needs to be applied to only 2 sheets named "Sheet1" and "Sheet2" (as attached). I don't know how to edit the VBA from all the sheets to only those two sheets . plz help. Please find below the 2 VBA.
(2) Can I insert the 2 VBA at one go?? ( i mean; not inserting the first VBA, run it, then delete it, then same thing for the second VBA), such that I can run whichever VBA at my desired time in one-two clicks. (i dont know, via macro or something?? ) please help

VBA 1
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
ws.Range("AI96") = "Sort"
ws.Range("AI97", "AI120").Formula = "=IF(J97=0,""ZZZ"",J97)"
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("AI97:AI120"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("I96:AI120")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("AI96", "AI120").Clear
Application.Goto ws.Range("A1"), True
Next

End Sub


VBA 2
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
ws.Range("AA125") = "Sort"
ws.Range("AA126", "AA149").Formula = "=IF(J126=0,""ZZZ"",J126)"
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("AA126:AA149"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("I125:AA149")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("AA125", "AA149").Clear
Application.Goto ws.Range("A1"), True
Next

End Sub

 

[Zot]

Why not just give each macro different name? Like
Sub SortAllSheets1
Sub SortAllSheets2

Then when you want to run macro just press Alt+F8 and select which macro to run.

If you want to apply only to Sheet1 ans Sheet2, you can modify like this

Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
If ws.Name = "Sheet1" or ws.Name = "Sheet2" Then
ws.Range("AI96") = "Sort"
ws.Range("AI97", "AI120").Formula = "=IF(J97=0,""ZZZ"",J97)"
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("AI97:AI120"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("I96:AI120")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("AI96", "AI120").Clear
Application.Goto ws.Range("A1"), True

ooo... It looks like it was may previous code ?
End If
Next

End Sub

 

[Bilaal xaka]

Why not just give each macro different name? Like
Sub SortAllSheets1
Sub SortAllSheets2

Then when you want to run macro just press Alt+F8 and select which macro to run.

If you want to apply only to Sheet1 ans Sheet2, you can modify like this

Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
If ws.Name = "Sheet1" or ws.Name = "Sheet2" Then
ws.Range("AI96") = "Sort"
ws.Range("AI97", "AI120").Formula = "=IF(J97=0,""ZZZ"",J97)"
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("AI97:AI120"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("I96:AI120")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("AI96", "AI120").Clear
Application.Goto ws.Range("A1"), True

ooo... It looks like it was may previous code ?
End If
Next

End Sub

Hi ZOT, yes well guessed. Your only previous code. I waited for your rep but none.
sorry to disturb again but Im not getting the VBA.
I have written it in detail below. Pleaseee check it and correct it. (Also please find attached Screenshots of them when tried to insert in the excel and its subsequent debugs)
Please corect it the same way as I will input it in the excel.
I am very weak in VBA.

.............................................................

Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
ws.Range("AI96") = "Sort"
ws.Range("AI97", "AI120").Formula = "=IF(J97=0,""ZZZ"",J97)"
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("AI97:AI120"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("I96:AI120")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("AI96", "AI120").Clear
Application.Goto ws.Range("A1"), True

End If
Next

End Sub
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
ws.Range("AA125") = "Sort"
ws.Range("AA126", "AA149").Formula = "=IF(J126=0,""ZZZ"",J126)"
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("AA126:AA149"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("I125:AA149")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Range("AA125", "AA149").Clear
Application.Goto ws.Range("A1"), True
Next

End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Edit a VBA
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.