INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Remove Workbook Specific Code

Remove Workbook Specific Code

(OP)
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.

CODE

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

RE: Remove Workbook Specific Code

hi,

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove Workbook Specific Code

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

RE: Remove Workbook Specific Code

Sorry. See correction in my original code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove Workbook Specific Code

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

RE: Remove Workbook Specific Code

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

Check the original code again. I ran it successfully.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove Workbook Specific Code

(OP)
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.

RE: Remove Workbook Specific Code

CODE

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   '
        If .FilterMode Then .ShowAllData    '
    End With

    Set ws = Nothing

End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Remove Workbook Specific Code

(OP)
That's it, thanks so much!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close