syntax of for...next loop

[dpaton05]

Is there anything wrong with the syntax of my for next loop?

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 String

'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 = Worksheets("home").ListObjects("tblCosting")
        
'        TableNumberRows = Rows("tblCosting")                                          'Worksheets("home").ListObjects("tblCosting").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

Thanks

 

[dpaton05]

I am such a monkey, got the link working. I will add a more recent file that I have been working on. Please help as now it doesn't seem to copy columns O-Q from the home sheet to columns to columns K-M of the august 2018 sheet, but appears to copy correctly to other sheets. I wish I could debug problems myself better. Thanks Norie.

https://drive.google.com/file/d/1Th6iP5CDOteONYk3qE69Rs_KCsw7386G/view?usp=sharing

 

[Norie]

I'll try and take a look at it later.

PS If it is copying correctly to other sheets then it could be a sheet specific problem.

 

[dpaton05]

I'll try and take a look at it later.

PS If it is copying correctly to other sheets then it could be a sheet specific problem.

What do you mean by sheet specific problem?

 

[Norie]

You say it's working for all the sheets apart from the 'august 2018' sheet, so perhaps that sheet has a particular problem.

 

[dpaton05]

I think I found multiple problems. I think it is in the formulas in of my home sheet. I am trying to debug them but I can't find what's wrong with them.

 

[dpaton05]

I think I found multiple problems. I think it is in the formulas in columns S, T and U of tblCosting in of my home sheet. I am trying to debug them but I can't find what's wrong with them.

 

[dpaton05]

Found a problem in one of the formulas and I fixed it. The only issue I am having now is still copying to the august 2018 sheet. If a row has a date in august 2018, every column of tblCosting will copy to the august 2018 sheet, except for the 3 total price rows in columns O-Q of the home sheet or columns K-M of the august 2018 sheet. Nothing is copied in those 3 columns to the monthly sheet, despite the formulas calculating the correct value on the home sheet. I have no idea why this is happening so can I get some help please?

Here is a recent file I am using with the problem I found fixed.

https://drive.google.com/file/d/1yA-ZftH5jXr7uxY6q0sF-G_Ls5C8Xjkt/view?usp=sharing
Thanks,
Dave