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

 

[dpaton05]

I get the following error with that: https://www.screencast.com/t/xxAzxkasJtl

 

[dpaton05]

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

 

[Michael M]

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 !!!

 

[dpaton05]

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:

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

 

[Michael M]

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

Application.CutCopyMode = False

 

[dpaton05]

I added that code to my cmdSort procedure and it didn't change anything. Here is a screenshot of what it is doing.

https://www.screencast.com/t/X7TkyBCurvS

 

[dpaton05]

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?

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

 

[Michael M]

Just select say, range "A1"

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

 

[dpaton05]

But that wasn't with my cmdSort procedure, it was with the procedure in post 27

 

[dpaton05]

Why is this code highlighting a box as I don't want it to?

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