Sort Code error in 2003

jordiejones

New Member
Joined
May 5, 2010
Messages
31
Hello All, I am an absolute n00b when it comes to VB. most of my code was written in MacroRecorder and edited to clean it up and make it more readable to a computer. This code works fine in 2007 but my store computers use 2003. Can someone please help me identify what I can change to make this work in 2003?

Line that I'm getting an error on:
Run-time error '438': Object doesn't support this property or method
Code:
ActiveWorkbook.Worksheets("Sales Recap ").Sort.SortFields.Clear

Code:
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+j
'
'!!!FILES WORK BEST WHEN STORED IN THE SAME ROOT ei. C:\My Documents\Manager's Folders\Store Manager\
'BEGIN OPEN CHILD WORKBOOKS
    Cells.Select
    Selection.Copy
    
    '!!!PLACE THE FULL ROOT OF YOUR FLASH METRICS FILE HERE
    Workbooks.Open Filename:="C:\Documents and Settings\Administrator\My Documents\Manager's Folders\JORDAN\VB Macros\Flash Metrics.xls"
    
   '!!!PLACE THE FULL ROOT OF YOUR TEMP BOOK FILE HERE
    Workbooks.Open Filename:="C:\Documents and Settings\Administrator\My Documents\Manager's Folders\JORDAN\VB Macros\TempBook.xls"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 'END OPEN CHILD WORKBOOKS
 
        
 'BEGIN CLEAR OLD DATA
    Windows("Flash Metrics.xls").Activate
    Range("A5:Q35").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
    End With
    Range("A40:Q70").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
    End With
    Range("A74:Q104").Select
    Selection.ClearContents
    With Selection.Interior
        .Pattern = xlNone
    End With
'END CLEAR OLD DATA
'BEGIN DAY METRIC SORT
    'BEGIN NETSALES SORT
    Windows("TempBook.xls").Activate
    Range("A4:B34").Select
    Range("B34").Activate
    Selection.Copy
    Windows("Flash Metrics.xls").Activate
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A14:B14").Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
    End With
    Range("A5:B35").Select
    ActiveWorkbook.Worksheets("Sales Recap ").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sales Recap ").Sort.SortFields.Add Key:=Range( _
        "B5:B35"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sales Recap ").Sort
        .SetRange Range("A5:B35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    'END NETSALES SORT
' more code....
End Sub

Thanks for your time - Jordan!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try replacing this part of the code...

Code:
    Range("A5:B35").Select
    ActiveWorkbook.Worksheets("Sales Recap ").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sales Recap ").Sort.SortFields.Add Key:=Range( _
        "B5:B35"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sales Recap ").Sort
        .SetRange Range("A5:B35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

with

Code:
With ActiveWorkbook.Worksheets("Sales Recap ").Range("A5:B35")
    .Sort key1:=Range("B5"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

By the way, your sheet name "Sales Recap " contains a space at the end of it. Is this a typo?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top