What is wrong with this code

[dpaton05]

I need some help. I am still learning to code and I don't yet fully understand vba. I have the following code and it is keeps giving me errors:

Sub cmdCopyr()
Application.ScreenUpdating = False

'declare variables
Dim TableLastRow As Long
Dim LastRow As Long
Dim TableNumberRows As Long
Dim iCounter As Long                                    'counter of for..next loop
Dim tblRows As Integer                                  'number of rows in tblCosting
Dim tblrow As ListRow                                   'specific row in tblCosting
Dim Combo As String                                     'Combo worksheet name
Dim sht As Worksheet
Dim Start As Long                                       'number of first row in tblCosting
Dim Costing As ListObject

'assign values to variables
Set sht = Worksheets("home")
TableLastRow = Split(Sheets("Home").ListObjects("tblCosting").DataBodyRange.Address, "$")(4)            'last row in tblCosting of home sheet
Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
'Costing =

TableNumberRows = ListObjects("tblCosting").Range.Rows.Count
Start = "5"

 
For iCounter = Start To TableLastRow Step 1
    ListObjects("tblCosting").Range.Cells(iCounter, 1).Resize(, 10).copy
    With Worksheets(Combo).Cells(LastRow, iCounter)
        .PasteSpecial Paste:=xlPasteValues
        .Columns("A").NumberFormat = "dd/mm/yyyy"
    End With
    ListObjects("tblCosting").Range.Cells(iCounter, 1).copy
        Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
    ListObjects("tblCosting").Range.Cells(iCounter, 30).Resize(, 32).copy
        Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
Next iCounter

        'Check if activities is selected in column I
        If Worksheets("home").Range("I5") = "Activities" Then
            Worksheets(Combo).Range("K" & LastRow).Value = Worksheets("home").Range("AC" & LastRow)          'copy formula to total column if activities are selected
            
            'Worksheets(Combo).Range("I" & Lastrow).ClearContents                                            'clear GST component if activities are selected
        Else
            Worksheets(Combo).Range("L" & LastRow).Formula = "=K" & LastRow & "*.1"                       'if activities are not selected, GST formula will be added in to column H
            Worksheets(Combo).Range("M" & LastRow).Formula = "=L" & LastRow & "+K" & LastRow        'if activities are not selected, add formula in to column I that will sum columns G + H
        End If
        
    Worksheets(Combo).Columns("H:J").NumberFormat = "$#,##0.00"                                             'format values in columns G to I of Combo
    Call SortDates                                                                                          'format cells to be in ascending date order

    Application.CutCopyMode = False                                                                         'cancel Cut or Copy mode

    
Application.ScreenUpdating = True
End Sub

The code needs to cycle through rows in a table called tblCosting. The table is designed to record costings. Here are some screen shots of the table:

https://www.screencast.com/t/U1SqbfXoS
https://www.screencast.com/t/tKcbK8r8

The table is located on a sheet called Home and currently has two rows of demo data. Data is entered up to column N and the rest is calculated automatically. The 3 columns on the end will also have data entered into them. Columns R to AC will be hidden from view as they are just used for calculation purposes. The three columns on the end will be visible however. There are sheets for every month of the year. When x number of rows have been entered, my supervisor wants to be able to press a button and have them all transferred to the relevant sheet depending on the date of the costing, for instance, if one row has the date in July 2018, that particular row will get transferred to the July 2018 sheet.

Only some of the rows are transferred and here is a screenshot of the sheets (they are all the same) where the information goes.

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


Can someone help me with the code please?

Thanks

 

[Dossfm0q]

I think listobject should be having reference worksheet linked with dot
i add worksheet

with sht
.xxxxx
.xxxxxx
end with
Call SortDates is to run external Macro code

Sub cmdCopyr()
Application.ScreenUpdating = False


'declare variables
Dim TableLastRow As Long
Dim LastRow As Long
Dim TableNumberRows As Long
Dim iCounter As Long                                    'counter of for..next loop
Dim tblRows As Integer                                  'number of rows in tblCosting
Dim tblrow As ListRow                                   'specific row in tblCosting
Dim Combo As String                                     'Combo worksheet name
Dim sht As Worksheet
Dim Start As Long                                       'number of first row in tblCosting
Dim Costing As ListObject


'assign values to variables
Set sht = Worksheets("home")


[SIZE=3][B][COLOR=#ff0000]With sht [/COLOR][/B][/SIZE]


TableLastRow = Split([B][COLOR=#FF0000].[/COLOR][/B]ListObjects("tblCosting").DataBodyRange.Address, "$")(4)            'last row in tblCosting of home sheet
Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
'Costing =


TableNumberRows = [SIZE=5][B][COLOR=#FF0000].[/COLOR][/B][/SIZE]ListObjects("tblCosting").Range.Rows.Count
Start = "5"


 
For iCounter = Start To TableLastRow Step 1
    [SIZE=5][B][COLOR=#ff0000].[/COLOR][/B][/SIZE]ListObjects("tblCosting").Range.Cells(iCounter, 1).Resize(, 10).Copy
    With Worksheets(Combo).Cells(LastRow, iCounter)
        .PasteSpecial Paste:=xlPasteValues
        .Columns("A").NumberFormat = "dd/mm/yyyy"
    End With
   [SIZE=4] [B][COLOR=#FF0000].[/COLOR][/B][/SIZE]ListObjects("tblCosting").Range.Cells(iCounter, 1).Copy
        Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
    [SIZE=4][B][COLOR=#FF0000].[/COLOR][/B][/SIZE]ListObjects("tblCosting").Range.Cells(iCounter, 30).Resize(, 32).Copy
        Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
Next iCounter


        'Check if activities is selected in column I
        If Worksheets("home").Range("I5") = "Activities" Then
            Worksheets(Combo).Range("K" & LastRow).Value = Worksheets("home").Range("AC" & LastRow)          'copy formula to total column if activities are selected
            
            'Worksheets(Combo).Range("I" & Lastrow).ClearContents                                            'clear GST component if activities are selected
        Else
            Worksheets(Combo).Range("L" & LastRow).Formula = "=K" & LastRow & "*.1"                       'if activities are not selected, GST formula will be added in to column H
            Worksheets(Combo).Range("M" & LastRow).Formula = "=L" & LastRow & "+K" & LastRow        'if activities are not selected, add formula in to column I that will sum columns G + H
        End If
        
    Worksheets(Combo).Columns("H:J").NumberFormat = "$#,##0.00"                                             'format values in columns G to I of Combo
    '[SIZE=3][B][COLOR=#ff0000]Call SortDates[/COLOR][/B][/SIZE]       'This to rnu another macro Code I add [B][COLOR=#008000]Apostrophe [/COLOR][/B]and now shown in green color '    remove '  Apostrophe 

to active macro cod
                                                                                   'format cells to be in ascending date order
[COLOR=#ff0000][B]End With 'end of Page Workshett[/B][/COLOR]
Application.CutCopyMode = False                                                                         'cancel Cut or Copy mode


    
Application.ScreenUpdating = True


End Sub

 

[dpaton05]

I don't think I quite understood your instructions. I have this code now and when I run it, it thinks for a while but then nothing happens.

Sub cmdCopyr()
Application.ScreenUpdating = False

'declare variables
Dim TableLastRow As Long
Dim LastRow As Long
Dim TableNumberRows As Long
Dim iCounter As Long                                    'counter of for..next loop
Dim tblRows As Integer                                  'number of rows in tblCosting
Dim tblrow As ListRow                                   'specific row in tblCosting
Dim Combo As String                                     'Combo worksheet name
Dim sht As Worksheet
Dim Start As Long                                       'number of first row in tblCosting
Dim Costing As ListObject

'assign values to variables
Set sht = Worksheets("home")

    With sht


        TableLastRow = Split(Sheets("Home").ListObjects("tblCosting").DataBodyRange.Address, "$")(4)            'last row in tblCosting of home sheet
        Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
        LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
        'Costing =
        
        TableNumberRows = ListObjects("tblCosting").Range.Rows.Count
        Start = "5"
        
         
        For iCounter = Start To TableLastRow Step 1
            .ListObjects("tblCosting").Range.Cells(iCounter, 1).Resize(, 10).copy
            With Worksheets(Combo).Cells(LastRow, iCounter)
                .PasteSpecial Paste:=xlPasteValues
                .Columns("A").NumberFormat = "dd/mm/yyyy"
            End With
            .ListObjects("tblCosting").Range.Cells(iCounter, 1).copy
                Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
            .ListObjects("tblCosting").Range.Cells(iCounter, 30).Resize(, 32).copy
                Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
        Next iCounter
        
                'Check if activities is selected in column I
                If Worksheets("home").Range("I5") = "Activities" Then
                    Worksheets(Combo).Range("K" & LastRow).Value = Worksheets("home").Range("AC" & LastRow)          'copy formula to total column if activities are selected
                    
                    'Worksheets(Combo).Range("I" & Lastrow).ClearContents                                            'clear gst component if activities are selected
                Else
                    Worksheets(Combo).Range("L" & LastRow).Formula = "=K" & LastRow & "*.1"                       'if activities are not selected, GST formula will be added in to column H
                    Worksheets(Combo).Range("M" & LastRow).Formula = "=L" & LastRow & "+K" & LastRow               'if activities are not selected, add formula in to column I that will sum columns G + H
                End If
                
            Worksheets(Combo).Columns("H:J").NumberFormat = "$#,##0.00"                                             'format values in columns G to I of Combo
            Call SortDates                                                                                          'format cells to be in ascending date order
    End With
    Application.CutCopyMode = False                                                                         'cancel Cut or Copy mode

    
Application.ScreenUpdating = True
End Sub

What is still wrong with it?

Thank you

 

[Michael M]

I think there should be a period before the List Objects

TableNumberRows = ListObjects("tblCosting").Range.Rows.Count

TO

TableNumberRows = .ListObjects("tblCosting").Range.Rows.Count

 

[dpaton05]

I think there should be a period before the List Objects

TableNumberRows = ListObjects("tblCosting").Range.Rows.Count

TO

TableNumberRows = .ListObjects("tblCosting").Range.Rows.Count

Thanks Michael.