VBA Copy and Paste random cells in a loop using VBA

2
votes

I will like to Copy and Paste cells in one worksheet with VBA, to another sheet in VBA using a loop.

For example, I will like to copy cell A2 in sheet2 to cell A1 in sheet1, cell B2 in sheet2 to cell B1 in sheet1. Then A3 in sheet2 to cell A1 in sheet1, cell B3 to cell B1 in sheet1. I will like to do this until the last row.

I tried the code below, but my data set is too large. Is there another alternative method? 

'Worksheets("Sheet1").Range("C28").Value = Worksheets("Sheet2").Range("B2").Value
3
vbaexcel
So you are over-writing values? – SJR
Yes, I am overwriting values. – Jane_Tampa
What exactly is your problem? You have posted a single line of code which is valid. Perhaps you should read How to Ask. – SJR
Your title indicates you want to paste random cells in a loop? – Davesexcel
Yea, that was my fault. I said random because most of the forums only copy and paste designated ranges. I want to be able to take any cell I want from one worksheet and paste it into any cell I want in another worksheet. – Jane_Tampa

3 Answers

1
votes

hi when im doing things like this i find it easiest to record a macro, then if you go to view your macros and edit them you can see the code that was created. that can be copied straight in to your vba project

1
votes

OK, I think you might want something along these lines.

Sub x()

Dim r As Long

With Sheet2
    For r = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
        Sheet1.Range("A1").Resize(, 2).Value = .Cells(r, 1).Resize(, 2).Value
        'presumably do something else?
    Next r
End With

End Sub
1
votes

Hi you can use Random number to generate row and column number

Sub test1()

 Dim LastRow, Lastcol As Long
 Dim wb As Workbook
 Dim ws1, ws2 As Worksheets


    Set wb = ThisWorkbook

    ' Sheet name where data is present
    Set ws1 = wb.Worksheets("OriginalSheet")
    ' Sheet name where data to be copied
    Set ws2 = wb.Worksheets("CopySheet")



    With ws1
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    End With



For i = 1 To Lastcol
    For j = 1 To LastRow

 ' Random number generation

 ' To create a random integer number between two values (range), you can use the following formula:

' Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

randrow = Int((50 - 10 + 1) * Rnd + 10)

randcol = Int((5 - 2 + 1) * Rnd + 2)

ws2.Cells(randrow, randcol).Value = ws1.Cells(j, i).Value


    Next
Next


End Sub