Sorting a table range using vba

[ipbr21054]

I have the following.
Row 1 is headers.
Row 2 is hidden.
My range is then A3:G3 then down the page.

I wish to sort this range from A-Z using column A
Its within a Table of which is Table 28

I have tried the following BUT i get a RTE1004 Application defined or Object defined error.
The code in blue below is shown when i debug it
Do you see where the issue is

Private Sub CommandButton1_Click()
    Workbooks.Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\MOTORCYCLES.xlsm")
    Worksheets("INVOICES").Activate
    Worksheets("INVOICES").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWorkbook.Close SaveChanges:=True
   
    Set WB = Workbooks.Open(fileName:="C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\MOTORCYCLES.xlsm")
        Workbooks("DR.xlsm").Sheets("INV").Range("G13").Copy
        WB.Sheets("INVOICES").Range("A3").PasteSpecial xlPasteValues
       
        Workbooks("DR.xlsm").Sheets("INV").Range("L16").Copy
        WB.Sheets("INVOICES").Range("B3").PasteSpecial xlPasteValues
       
        Workbooks("DR.xlsm").Sheets("INV").Range("L15").Copy
        WB.Sheets("INVOICES").Range("C3").PasteSpecial xlPasteValues
       
        Workbooks("DR.xlsm").Sheets("INV").Range("H52").Copy
        WB.Sheets("INVOICES").Range("D3").PasteSpecial xlPasteValues
       
        Workbooks("DR.xlsm").Sheets("INV").Range("H53").Copy
        WB.Sheets("INVOICES").Range("E3").PasteSpecial xlPasteValues
       
        Workbooks("DR.xlsm").Sheets("INV").Range("L13").Copy
        WB.Sheets("INVOICES").Range("F3").PasteSpecial xlPasteValues
       
        Workbooks("DR.xlsm").Sheets("INV").Range("L4").Copy
        WB.Sheets("INVOICES").Range("G3").PasteSpecial xlPasteValues
       
    Dim X As Long
        Application.ScreenUpdating = False
    With Sheets("INVOICES")
        If .AutoFilterMode Then .AutoFilterMode = False
        X = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A2:G" & X).Sort Key1:=.Cells(A3, ColumnNum), Order1:=xlAscending, Header:=xlGuess, dataoption1:=xlSortTextAsNumbers
    End With
       
        WB.Close True
       
        Workbooks("DR.xlsm").Sheets("INV").Range("G2").Select
        Application.CutCopyMode = False
        ActiveWorkbook.Save


End Sub

 

[DanteAmor]

In key1 you must reference a cell.
If you are in a table, you must indicate from row 1, Heade:=XlYes.
The row you have hidden will not be sorted.

Try this:

  Dim x As Long
  Application.ScreenUpdating = False
 
  With Sheets("INVOICES")
    If .AutoFilterMode Then .AutoFilterMode = False
    x = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("A1:G" & x).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, DataOption1:=xlSortTextAsNumbers
  End With

 

[ipbr21054]

Worked a treat,i was close but not close enough.

Thanks