• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Edit a VBA

bilaal xaka

New Member
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
>>> use code - tags <<<

Code:
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
Code:
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    2.7 KB · Views: 3
Last edited by a moderator:
bilaal xaka
Did You reread Forum Rules as I gave a hint about an one week ago?
 
bilaal xaka
Did You reread Forum Rules as I gave a hint about an one week ago?
Bro, I read it and Im trying my best to abide by the rules.
Can you tell me where I am failing to do so plz
 
bilaal xaka
I won't copy & paste same list which You can see.
Please, reread: How to get the Best Results at Chandoo.org , Please Don't and Always
After each sentence, ask from Yourself - have You done so?
 
1. Change:
Code:
For Each ws In Sheets
to:
Code:
For Each ws In Sheets(Array("Sheet1", "Sheet2"))
in both macros.

2. Rename the two macros so that they're different, say SortSheetsAI and SortSheetsAA, then add a new macro:
Code:
Sub AllSorts()
SortSheetsAI
SortSheetsAA
End Sub

Now you can run one of the original macros by themseleves or both together by running AllSorts.

Cross post, sure, but supply links to all of them please.
 
Back
Top