Copy and paste cells until reach end of data

Closed
Beksta - Apr 14, 2010 at 01:33 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Apr 15, 2010 at 12:53 PM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 14, 2010 at 03:20 PM
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
0
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?
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 15, 2010 at 11:54 AM
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.
0
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")
0

Didn't find the answer you are looking for?

Ask a question
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 15, 2010 at 12:11 PM
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
0
I have done so but am still encountering the same problem
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 15, 2010 at 12:21 PM
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
0
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!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 15, 2010 at 12:53 PM
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.
0