Sorting a table using vba code

dpaton05 · Nov 11, 2018

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

Michael M · Nov 11, 2018

What is actually in cell Q5 on the Home sheet ??

dpaton05 · Nov 11, 2018

Q5: =CONCATENATE(O5," ",P5)
P5: =TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "yyyy")
O5: =TEXT(DATE(YEAR(A5),IF(DAY(A5)<26,MONTH(A5),MONTH(A5)+1),1), "mmmm")
A5: Contains the date I need to sort the table by

The sheet names are in format mmmm yyyy

dpaton05 · Nov 11, 2018

The reporting month goes from the 26th of the previous month to the 25th of the current month.

dpaton05 · Nov 11, 2018

Michael M · Nov 11, 2018

Maybe this then

Code:

    Range("combo").Sort key1:=Range("Date of work"), order1:=xlAscending, Header:=xlYes

dpaton05 · Nov 11, 2018

Michael M said:
Maybe this then

Code:

Range("combo").Sort key1:=Range("Date of work"), order1:=xlAscending, Header:=xlYes

I get the error, Method Range object_worksheet failed, and I have no idea what is wrong.

Michael M · Nov 11, 2018

One of the 2 Named Ranges is incorrect or not Valid.
Instead of using the named ranges try using the actual range in your sort and makes sure it works.
So....what range does "combo" encompass ?
Is that correcty identified in Q5 ??
what range does "Date of work" encompass ?
Is that correcty identified ??

dpaton05 · Nov 11, 2018

Here is my code for the copy button. Date of work is the name of the table column that has the date. Each month has a different name table but the same column names.

Q5 gives me the "month year" and it is correctly identified. My code works to sort the data into the correct sheets but stops working when I try and sort the tables by date.

Code:

Sub cmdCopy()

    'turn screen updating off
    Application.ScreenUpdating = False
    
    'declare variables
    Dim Lastrow As Long                                                     'number of first empty row in column B of Combo
    Dim combo As String                                                     'Combo worksheet name
    Dim SortTable As String                                                 'Table name that needs sorting
    
    'assign values to variables
    combo = Worksheets("Home").Range("Q5")                                  'string in cell Q5 of Home worksheet
    Lastrow = Worksheets(combo).Cells(Rows.Count, "B").End(xlUp).Row + 1    'number of first empty row in column B of Combo
    SortTable = Worksheets("Home").Range("R5")                              'combination of month and year in cell R5 as the name appears on the sheet names
    
    '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

    '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 cmdsort
                Range("combo").Sort key1:=Range("Date of work"), order1:=xlAscending, Header:=xlYes
            
    'cancel Cut or Copy mode
    Application.CutCopyMode = False

    'turn screen updating on
    Application.ScreenUpdating = True

End Sub

dpaton05 · Nov 11, 2018

Here is the line of code that is highlighted when I try and execute it.

Range(combo).Sort key1:=Range("Date of work"), order1:=xlAscending, Header:=xlYes

Sorting a table using vba code

dpaton05

Well-known Member

Excel Facts

Michael M

Well-known Member

dpaton05

Well-known Member

dpaton05

Well-known Member

dpaton05

Well-known Member

Michael M

Well-known Member

dpaton05

Well-known Member

Michael M

Well-known Member

dpaton05

Well-known Member

dpaton05

Well-known Member

Similar threads

Forum statistics

Share this page

Sorting a table using vba code

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock