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]

Your destination sheet is the same as your source sheet.... correct?

 

[Multim]

Yes correct

 

[MARK858]

So are you just converting the cells to values? if yes then try

Sub Title()
    Dim srcWS As Worksheet
 
    Set srcWS = Workbooks("Inventory Import (Full) - Washout Template.xls").Worksheets("Routing Info")
 
    With srcWS.Range("A2:BI" & srcWS.Range("A" & Rows.Count).End(xlUp).Row)
        .Value = .Value
    End With

End Sub

If something else then please elaborate as at the moment it looks like your code could be overwriting the cells in column B.

Literally going by what your code looks like it would be

Sub Title2()
    Dim srcWS As Worksheet
  
    Set srcWS = Workbooks("Inventory Import (Full) - Washout Template.xls").Worksheets("Routing Info")
 
    With srcWS.Range("A2:BI" & srcWS.Range("A" & Rows.Count).End(xlUp).Row)
        srcWS.Cells(srcWS.Rows.Count, "B").End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

End Sub

 

[MARK858]

Please note I have edited the last code as I missed a period / full stop

 

[Multim]

Hi Mark

Thank you,

To explain further I have a list of part numbers in column A, the part number is repeated 3 times - so A2 to A4 and so on for the next part number.

The above code is on a button in the <span>"Routing Info"</span> worksheet and copies the range <span>"B2:AI5"</span> then pastes it into the next empty cell in column B (so 4 rows of data line up to the 4 part numbers), I then repeatedly push this button until the last set of part numbers in A.

I am trying to change the code so that it pastes the range <span>"B2:AI5"</span> repeatedly until the last set of part numbers, this is where i am getting lost.

Hope this makes sense

 

[MARK858]

so 4 rows of data line up to the 4 part numbers

But there are only 3 part numbers?

the part number is repeated 3 times - so A2 to A4

or are you saying there is no header so A1 to A4

 

[Multim]

Sorry Mark A2 to A5 - 4 lines

 

[MARK858]

If I've read it right try the code below on a copy of your data

Sub Title3()
    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
        With srcWS.Range("B2:AI5")
            srcWS.Cells(i, "B").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    Next

End Sub

 

[Multim]

Hi Mark

Tried the code - see result below, it is pasting the first row multiple times then the rest on the last 3 rows.