VBA to Paste until last cell in column A

[Multim]

Hello

I have the code below but cant workout how to code it to paste until the last data cell in column A.

Sub Title()
    Dim srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Workbooks("Inventory Import (Full) - Washout Template.xls").Worksheets("Routing Info")
    Set desWS = Workbooks("Inventory Import (Full) - Washout Template.xls").Worksheets("Routing Info")
    With desWS
        srcWS.Range("B2:AI5").Copy
        .Cells(.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Any help would be appreciated

Thank you

 

[MARK858]

Sorry that's my fault

Sub Title4()
    Dim srcWS As Worksheet, i As Long
    Application.ScreenUpdating = False

    Set srcWS = Workbooks("Inventory Import (Full) - Washout Template.xls").Worksheets("Routing Info")

    For i = 6 To srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row - 3 Step 4
        With srcWS.Range("B2:AI5")
            srcWS.Cells(i, "B").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    Next

End Sub

 

[Multim]

Thank you, Spot on Mark

I am still learning, does the "Step" part of the line instruct to paste every 4 lines?

Thanks again

 

[MARK858]

You're welcome

I am still learning, does the "Step" part of the line instruct to paste every 4 lines?

It tells it to increase the counter (i in this case) by 4 on each iteration, when it is omitted it defaults to 1.
i in this case is the row number

 

[Multim]

So if i had 6 rows of data to paste (sometimes I do) I change the range data and the the step to 6?

 

[MARK858]

Yes, or if you are being clever use something like

x = srcWS.Range("B2:B" & srcWS.Range("B" & srcWS.Rows.Count).End(xlUp).Row).Rows.Count

then base everything on x.

Something like

Sub TitleNew()
    Dim srcWS As Worksheet, i As Long, x As Long
    Application.ScreenUpdating = False

    Set srcWS = Workbooks("Inventory Import (Full) - Washout Template.xls").Worksheets("Routing Info")

    x = srcWS.Range("B2:B" & srcWS.Range("B" & srcWS.Rows.Count).End(xlUp).Row).Rows.Count
 
    For i = x + 2 To srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row -(x-1) Step x
        With Range(srcWS.Cells(2, "B"), srcWS.Cells(x + 1, "AI"))
            srcWS.Cells(i, "B").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    Next

End Sub