How to split the last row of cells in one sheet and copy to another sheet's specific location?

[KayWill]

I have reviewed several similar topics and there is something I am definitely doing wrong. I have a worksheet called "ShipDtls" that contains data. I want the last row, columns 16 thru 20, data split (see link w/example:

) {link: https://drive.google.com/file/d/0B7NhvPKqAJyIbUVtdFZnUmlGMHM/view?usp=sharing} and

I want to paste the data in those columns to the destination sheet called "Form" but I want it to paste starting in row 22 (see link w/example:

) {link: https://drive.google.com/file/d/0B7NhvPKqAJyIWHdJdUdLemlnaUU/view?usp=sharing}

I tried "hijacking" the following code but it's not working, keep getting 1004 error code:

Private Sub Paste2FRM()
    Dim w1 As Worksheet, wR As Worksheet
    Dim lr As Long, r As Long, Sp, n As Long
    Application.ScreenUpdating = False
    Set w1 = Worksheets("ShipDtls")
    Set wR = Worksheets("Form")
    wR.UsedRange.Clear
    w1.UsedRange.Copy wR.Range("Q3")
    lr = wR.Cells(Rows.Count, 1).End(xlUp).Row
    For r = lr To 1 Step -1
        If InStr(wR.Cells(r, 17), ",") > 0 Then
            Sp = Split(wR.Cells(r, 17), ",")
            wR.Rows(r + 21).Resize(UBound(Sp)).Insert
            wR.Cells(r, 21).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
            wR.Cells(r, 1).Resize(UBound(Sp) + 1) = wR.Cells(r, 1)
        End If
    Next r
    wR.UsedRange.Columns.AutoFit
    wR.Activate
    Application.ScreenUpdating = True
End Sub

Anyone's assistance to point me in the right direction will be GREATLY appreciated.

 

[skywriter]

I would take a shot at helping you, but you are posting pictures. I would prefer you share the sheet, so I can test the code on the actual sheet and see where the issues lie. If you have sensitive data, just replace it with dummy data.

Thanks.

 

[JLGWhiz]

This is probably not exactly what you want, but maybe it will lead you to a final solution. It should be noted that numbers separated by commas MUST have a space after the comma or Excel will automatically ignore the comma and make it one big number. So the Split function would not work as desired on any of those cells without the space after the comma. It does not matter with text entries, except that the spaces will be picked up in the cell value, which means the Trim function might be needed later to manipulate any of that data. I'll stop here before I create an overload.

Private Sub Paste2FRM()
    Dim w1 As Worksheet, wR As Worksheet
    Dim lr As Long, r As Long, Sp, n As Long
    Application.ScreenUpdating = False
    Set w1 = Worksheets("ShipDtls")
    Set wR = Worksheets("Form")
    wR.UsedRange.Clear
    w1.UsedRange.Copy wR.Range("Q3")
    lr = wR.Cells(Rows.Count, 1).End(xlUp).Row
    For r = lr To 1 Step -1
        If InStr(wR.Cells(r, 17), ",") > 0 Then
            Sp = Split(wR.Cells(r, 17), ",")
            wR.Rows(r + 21).Resize(UBound(Sp)).Insert
            wR.Cells(r, 21).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
            wR.Cells(r, 1).Resize(UBound(Sp) + 1) = wR.Cells(r, 1)
        End If
    Next r
    wR.UsedRange.Columns.AutoFit
    wR.Activate
    Application.ScreenUpdating = True
End Sub