Sorting a table using vba code

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,362
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am trying to sort a table by the date column with this code:

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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I have a little sub procedure to sort the data. What is wrong with this code:

Sub SortDates()

Dim StRw As Integer, EndRw As Integer
StRw = 4 ' Starting Row
EndRw = Range("E65500").End(xlUp).Row
Rows(StRw & ":" & EndRw).Select
Selection.Sort Key1:=Range("A:A"), Order1:=xlAscending
End Sub
 
Upvote 0
Post #21 usually means you don't have a worksheet by that name.....As mentioned previously, check to make sure the names that refer to a sheet are correct !!

Post #22 code works fine for me !!!
 
Upvote 0
I have some code that works for me now. Only problem is when it is run, some areas in the spreadsheet are highlighted. There are two boxes that are highlighted and one of them disappears when you alt tab out of the spreadsheet and return to it and the other one you have to physically click on the sheet to get it to go away. How do I get rid of those? This is the copy code I am using that is making the selections:

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

Thanks,
Dave
 
Upvote 0
Highlighted by "crawling ants" ??
If so, put this before the last lin in your code

Code:
Application.CutCopyMode = False
 
Upvote 0
I am also trying to copy the same information to an additional sheet called All Costings. The reason for this is I thought I could use all the data to create a pivot table. I tried to copy and paste the paste part of the procedure and it wouldn't work. What do I need to do to this code to get it to paste the same information into the bottom of a All Costings sheet?




Code:
Sub cmdCopy()

    'turn screen updating off
    Application.ScreenUpdating = False
    
    'declare variables
    Dim Lastrow As Long                                                     'number of first empty row in column A of Combo
    Dim combo As String                                                     'Combo worksheet name
    Dim SecondLastrow As Long                                               'number of first empty row in column A of All costings worksheet
    
    'assign values to variables
    combo = Worksheets("Home").Range("Q5")
    Lastrow = Worksheets(combo).Cells(Rows.Count, "A").End(xlUp).Row + 1    'number of first empty row in column A of Combo
    SecondLastrow = Worksheets("All Costings").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'copy values in cells A5 to C5 of Home worksheet
    Worksheets("Home").Range("a5:c5").copy

    'work with cell at intersection of LastRow and column A of Combo
    With Worksheets(combo).Cells(Lastrow, 1)
        'paste values
        .PasteSpecial Paste:=xlPasteValues
        'format date
        .NumberFormat = "dd/mm/yyyy"
    End With
    
    
    
    
    'work with cell at intersection of SecondLastRow and column A of All Costings worksheet
    With Worksheets("All Costings").Cells(SecondLastrow, 1)
        'paste values
        .PasteSpecial Paste:=xlPasteValues
        'format date
        .NumberFormat = "dd/mm/yyyy"
    End With
    
    

    'assign number of first empty row in column B of Combo to Lastrow
    Lastrow = Sheets(combo).Cells(Rows.Count, "A").End(xlUp).Row
    
    'copy value in cell J5 of Home worksheet
    Worksheets("Home").Range("j5").copy
    
    'paste value in cell at intersection of Lastrow and column D of Combo
    Worksheets(combo).Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues
    
    'format values in column D of Combo
    Worksheets(combo).Columns("D:D").NumberFormat = "$#,##0.00"
    
    'format cells to be in ascending date order
    Call SortDates

    'cancel Cut or Copy mode
    Application.CutCopyMode = False

    'turn screen updating on
    Application.ScreenUpdating = True

End Sub

The second line of paste special is highlighted when it throws up an error:

.PasteSpecial Paste:=xlPasteValues
 
Last edited:
Upvote 0
Just select say, range "A1"

Code:
Sub cmdSort()
'
   'Sorting procedure
'
    Dim combo As String
        combo = Worksheets("Home").Range("Q5")
        
    ActiveWorkbook.Worksheets(combo).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(combo).Sort.SortFields.Add2 Key:=Range( _
        "A4:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(combo).Sort
        .SetRange Range("A3:D1000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
[color=red]Worksheets(combo).Range("A1").Select
  Application.CutCopyMode = False[/color]
End Sub
 
Upvote 0
But that wasn't with my cmdSort procedure, it was with the procedure in post 27
 
Upvote 0
Why is this code highlighting a box as I don't want it to?

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

Application.CutCopyMode = False

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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