Next without For

[dpaton05]

I have some code that when I try and run it, I get an error message saying next without for and it has the next highlighted. What am I missing?

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 Start As Long                                       'number of first row in tblCosting

'assign values to variables
TableLastRow = Worksheets("home").ListObjects("tblCosting").Rows(Rows.Count).Row                        'last row in tblCosting of home sheet
LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
TableNumberRows = ListObjects("tblCosting").Range.Rows.Count
Start = "5"

'    With tblCosting.Range
 '       TableLastRow = .Rows(Rows.Count).Row
'    End With

For iCounter = Start To TableLastRow Step 1
    ListObjects("tblCosting").Range("A" & iCounter & ":" & "J" & iCounter).copy
    With Worksheets(Combo).Cells(LastRow, iCounter)
        .PasteSpecial Paste:=xlPasteValues
        .Columns("A").NumberFormat = "dd/mm/yyyy"
    ListObjects("tblCosting").Range("O" & iCounter).copy
        Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
    ListObjects("tblCosting").Range("AD" & iCounter & ":" & "AF" & iCounter).copy
        Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
[COLOR=#ff0000]Next [/COLOR]iCounter
    
Application.ScreenUpdating = True
End Sub

 

[Akuini]

You missed "End With" for "With Worksheets(Combo)"

 

[dpaton05]

You missed "End With" for "With Worksheets(Combo)"

Thanks

 

[Akuini]

Your welcome.

 

[dpaton05]

Your welcome.

Now I am getting an error saying object doesn't support this property or method and i have changed the colour of the text that is highlighted.

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 Start As Long                                       'number of first row in tblCosting

'assign values to variables
[COLOR=#ff0000]TableLastRow = Worksheets("home").ListObjects("tblCosting").Rows(Rows.Count).Row  [/COLOR]                      'last row in tblCosting of home sheet
LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
TableNumberRows = ListObjects("tblCosting").Range.Rows.Count
Start = "5"

'    With tblCosting.Range
 '       TableLastRow = .Rows(Rows.Count).Row
'    End With

For iCounter = Start To TableLastRow Step 1
    ListObjects("tblCosting").Range("A" & iCounter & ":" & "J" & iCounter).copy
    With Worksheets(Combo).Cells(LastRow, iCounter)
        .PasteSpecial Paste:=xlPasteValues
        .Columns("A").NumberFormat = "dd/mm/yyyy"
    End With
    ListObjects("tblCosting").Range("O" & iCounter).copy
        Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
    ListObjects("tblCosting").Range("AD" & iCounter & ":" & "AF" & iCounter).copy
        Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
Next iCounter
    
Application.ScreenUpdating = True
End Sub

Can someone help me with this please?

 

[RoryA]

A Listobject doesn't have a Rows property. You need .Range.Rows and you need to qualify the Rows.Count so that it applies to the same range.

 

[Akuini]

If you want to get the last row of a table you can use this:

LastRow=split(Sheets("sheet1").ListObjects("Table1").databodyrange.Address,"$")(4)

 

[dpaton05]

I have this code now and I get an error saying Invalid procedure call or argument and the highlighted line of code has been coloured differently:

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

'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

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

 
For iCounter = Start To TableLastRow Step 1
 [COLOR=#ff0000]   ListObjects("tblCosting").Range("A" & iCounter & ":" & "J" & iCounter).copy[/COLOR]
    With Worksheets(Combo).Cells(LastRow, iCounter)
        .PasteSpecial Paste:=xlPasteValues
        .Columns("A").NumberFormat = "dd/mm/yyyy"
    End With
    ListObjects("tblCosting").Range("O" & iCounter).copy
        Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
    ListObjects("tblCosting").Range("AD" & iCounter & ":" & "AF" & iCounter).copy
        Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
Next iCounter
    
Application.ScreenUpdating = True
End Sub

Could I get some help please?

 

[RoryA]

I suggest in future you post these as separate questions as they are in no way related to the title of this thread.

You need to use something like:

ListObjects("tblCosting").Range.Cells(iCounter , 1).Resize(, 10).copy