Sorting a table using vba code

[dpaton05]

I am trying to sort a table by the date column with this code:

    'declare variables
    Dim combo As String                                                     'Combo worksheet name
    
    'assign values to variables
    combo = Worksheets("Home").Range("Q5")                                  'string in cell Q5 of Home worksheet
    
    Range(combo).CurrentRegion.Sortkey1:=Range("Date of work"), order1:=xlAscending, Header:=xlYes

Combo has the relevant sheet name stored within it and the table goes up column Q. Can anyone tell me what I have done wrong with this code please as it gives me a syntax error and highlights the range(combo) line of code?

Thanks,
Dave

 

[Michael M]

What box ???
Did you apply this line of code in your last macro

Worksheets(combo).Range("A1").Select

 

[dpaton05]

That seems to partially work but half way through the procedure, it throws up an error saying object doesn't support this property or method. This is the line of code that is highlighted:

    ActiveWorkbook.Worksheets(Combo).Sort.SortFields.Add2 Key:=Range( _
        "A4:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

 

[Michael M]

It worked previously, what else have you changed ???

 

[dpaton05]

I don't think it did work previously. I have only just decided to add the sort procedure in. The copy procedure worked, but not the sort procedure.

 

[dpaton05]

Can you see anything wrong with this code as when I comment the sort sub out, it works, except for the sorting?

Sub cmdSort()
'
   'Sorting procedure
'
    Dim Combo As String
        Combo = Worksheets("Home").Range("Q5")
        
        
        
        
    Range("A3:D1920").Select
        
    Worksheets(Combo).Sort.SortFields.Clear
    Worksheets(Combo).Sort.SortFields.Add2 Key:=Range( _
        "A4:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Worksheets(Combo).Sort
        .SetRange Range("A3:D1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Application.CutCopyMode = False

End Sub

 

[dpaton05]

I think I have found what is going on. With this code

   Sheets("August 2018").Sort key1:=Range("A4"), _
      order1:=xlAscending, Header:=xlYes

it sorts the August 2018 sheet but when i try this

   Sheets(Combo).Sort key1:=Range("A4"), _
      order1:=xlAscending, Header:=xlYes

I am told named argument not found, even though I have dimmed combo as a variable to match the sheet name.


I made a macro to try and get it working

Sub SortDates()
'
' sortDates Macro
'

  'Set up your variables and turn off screen updating.
   Dim iCounter As Integer
   Dim Combo As String
        Combo = Worksheets("Home").Range("Q5")
   Application.ScreenUpdating = False
   
  
   'Sort the rows based on the data in column C

   Sheets(Combo).Sort key1:=Range("A4"), _
      order1:=xlAscending, Header:=xlYes
   
   'Clear out the temporary sorting value in column C, and turn screen updating back on.
   'Columns(3).ClearContents
   Application.ScreenUpdating = True
End Sub

 

[dpaton05]

the Last box was the code I got the code snippets above it.

 

[Michael M]

Use

Sub cmdSort()
'
   'Sorting procedure
'
    Dim Combo As String
        Combo = Worksheets("Home").Range("Q5")
    'Range("A3:D1920").Select
    With Worksheets(Combo)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A4:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets(Combo).Sort
        .SetRange Range("A3:D1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
Application.CutCopyMode = False

End Sub

 

[dpaton05]

That works perfectly, thank you!!

 

[Michael M]