Copy and paste cells until reach end of data

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi, This is my first time trying to program in VB and I have no idea what I am doing! I have data that I would like to copy and paste within the same worksheet.
If this makes any sense I want to copy cells A1, B7, B9, & B10 and paste them to K1,L1,M1,N1 then copy A21, B27,B29,B30 and paste to K2,L2,M2,N2 etc until the end of the data.
Any help would be greatly appreciated.

9 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Sub pasteCells()

Dim lMaxRows As Long ' max rows of data
Dim lRowBeanCounter As Long ' to count row
Dim lTargetRow As Long

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lRowBeanCounter = 1 To lMaxRows Step 20
    
    lTargetRow = lTargetRow + 1
    
    With Sheet2
        .Cells(lTargetRow, "K") = Cells(1 + (lTargetRow - 1) * 20, "A")
        .Cells(lTargetRow, "L") = Cells(7 + (lTargetRow - 1) * 20, "B")
        .Cells(lTargetRow, "M") = Cells(9 + (lTargetRow - 1) * 20, "B")
        .Cells(lTargetRow, "N") = Cells(10 + (lTargetRow - 1) * 20, "B")

    End With
        
    Next lRowBeanCounter

End Sub
Thank you for your help. It looks good but when I try to run the program i am given an error '424' object required. Can anyone help me with that?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It will give you option of debug, See what line it highlight. Also could you please upload a sample file on some shared site like https://authentification.site and post back here the link.
Thank you rizvisa1
the data is uploaded here is the link:
https://authentification.site/files/21957132/data_sample.xlsx
the line that is highlighted is:
.Cells(lTargetRow, "K") = Cells(1 + (lTargetRow - 1) * 20, "A")
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Add a new sheet names Sheet2


If you want on the same sheet then use this code

Sub pasteCells()

Dim lMaxRows As Long ' max rows of data
Dim lRowBeanCounter As Long ' to count row
Dim lTargetRow As Long
    
    On Error Resume Next
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lRowBeanCounter = 1 To lMaxRows Step 20
    
    lTargetRow = lTargetRow + 1
    
    Cells(lTargetRow, "K") = Cells(1 + (lTargetRow - 1) * 20, "A")
    Cells(lTargetRow, "L") = Cells(7 + (lTargetRow - 1) * 20, "B")
    Cells(lTargetRow, "M") = Cells(9 + (lTargetRow - 1) * 20, "B")
    Cells(lTargetRow, "N") = Cells(10 + (lTargetRow - 1) * 20, "B")

    Next lRowBeanCounter

End Sub
I have done so but am still encountering the same problem
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I used your file and added the macro and ran it without any issue

See if you can spot what is different between your file and this updated file
https://authentification.site/files/21957442/data_sample.xlsm
the first version did not have - on error resume next - it seems to be working now.
thank you so much, you have been extremely helpful. and have saved me so much frustration!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
On error resume next is not needed and should not be there either as there is no reason for error to occur. If an error does occur, it needs to be addressed. It was just an oversight on my part. I just re-ran the code without that line and it runs fine.