Remove Workbook Specific Code 1

[Volk359]

Greetings All,

I wrote a macro to clean up some of our spreadsheets, part of the process is doing a sort when it's done. The problem is it's workbook or worksheet specific and my VBA isn't good enough to be able to remove the part of the code to make it work in all work books.

  ActiveWorkbook.Worksheets("CGF").Sort.SortFields.Add Key:=Range("A2:A63"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("CGF").Sort
        .SetRange Range("A1:T63")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I tried removing Worksheets("CGF") and ActiveWorkbook.Worksheets("CGF") from the code and it won't run in my other files. FYI, this macro is in my PERSONAL.XLSB file.

Would appreciate any help.

Thanks

 

[SkipVought]

hi,

Sub genTest()
'The active sheet must be the sheet containing the table to be sorted
'There must be no other data contiguous with the data in the table
    Dim ws as WorkSheet

    Set ws = ActiveWorkbook.ActiveSheet

    With ws
        .Sort.SortFields.Add _
            Key:=.Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown)), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .Sort
            .SetRange ws.Range("A1").CurrentRegion
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

    Set ws = Nothing

End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Volk359]

Looks good but for some reason it bombs at .SetRange .Range("A1").CurrentRegion

 

[SkipVought]

Sorry. See correction in my original code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Volk359]

Sorry, for some reason it doesn't like the .Apply

 

[SkipVought]

Sorry, I missed the key range (column A).

Check the original code again. I ran it successfully.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Volk359]

There, that's the ticket. Awesome, thanks! One last item, if you please. I'd also like to have the macro clear the filters in the sheet:

Rows("1:1").Select
ActiveSheet.ShowAllData

Which works fine however if none of the columns are filtered then it bombs. I'm familiar with some programing but this VBA stuff is like Greek to me. Can an IF/THEN statement be put in to this, something like:

IF no filter THEN do nothing ELSE do routine.

Thanks.

 

[SkipVought]

Sub genTest()
'The active sheet must be the sheet containing the table to be sorted
'There must be no other data contiguous with the data in the table
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.ActiveSheet

    With ws
        .Sort.SortFields.Add _
            Key:=.Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown)), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        With .Sort
            .SetRange ws.Range("A1").CurrentRegion
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With   '[b]
        If .FilterMode Then .ShowAllData    '[/b]
    End With

    Set ws = Nothing

End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Volk359]

That's it, thanks so much!