copy and paste cells until reach end of data Closed

Question

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.

rizvisa1 · Answer

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

Beksta · Answer

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?

rizvisa1 · Answer

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.

Beksta · Answer

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")

rizvisa1 · Answer

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

Beksta · Answer

I have done so but am still encountering the same problem

rizvisa1 · Answer

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

Beksta · Answer

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!

rizvisa1 · Answer

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.

Copy and paste cells until reach end of data

9 responses

Similar discussions

Subscribe To Our Newsletter!