Results 1 to 3 of 3

VBA for generic macro to sort active worksheet

This is a discussion on VBA for generic macro to sort active worksheet within the Excel Questions forums, part of the Question Forums category; Good day, A portion of one of my macros is to sort the active worksheet, after a series of other ...

  1. #1
    New Member
    Join Date
    Jun 2007
    Location
    New Brunswick
    Posts
    30

    Default VBA for generic macro to sort active worksheet

    Good day,

    A portion of one of my macros is to sort the active worksheet, after a series of other steps from bring in some Oracle information. This macro may need to be applied to multiple sheets within a workbook, which has made me hesitant to rename the sheet.


    The portion of the code is shown below, I am trying to get "Sheet1" out of the formula and essentially make it ActiveWorksheet (ie. generic)... but I seem to be missing something. Any ideas on how to do this?


    Cheers,
    Graeme,




    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell. _
    Offset(0, 6).Range("A1:A99999"), SortOn:=xlSortOnValues, Order:=xlAscending _
    , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange ActiveCell.Offset(-1, 0).Range("A1:L100000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

  2. #2
    Board Regular Tinbendr's Avatar
    Join Date
    Jul 2010
    Location
    CTZ USA
    Posts
    714

    Default Re: VBA for generic macro to sort active worksheet

    Change ActiveWorkbook.Worksheets("Sheet1") to ActiveSheet.

    But if you know the sheets you need to sort, you can loop through them without having to select the worksheet first.
    David - Work 2007/Home 2010

  3. #3
    New Member
    Join Date
    Jun 2007
    Location
    New Brunswick
    Posts
    30

    Default Re: VBA for generic macro to sort active worksheet

    Perfect, Thanks.

    I was typing ActiveWorksheet


    Cheers,
    Graeme,

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com