VBA Copy and Paste random cells in a loop using VBA

Question

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

What exactly is your problem? You have posted a single line of code which is valid. Perhaps you should read How to Ask. — SJR
– SJR, Commented Dec 18, 2017 at 14:19
Your title indicates you want to paste random cells in a loop? — Davesexcel
– Davesexcel, Commented Dec 18, 2017 at 14:28
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
– Jane_Tampa, Commented Dec 18, 2017 at 14:41

mike · Accepted Answer · 2017-12-18 14:03:45Z

1

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

answered Dec 18, 2017 at 14:03

mike

135 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jane_Tampa Over a year ago

Hi. Yes, I tired to do that. The only problem I had was that the macro did not loop. Maybe I did it wrong, I will have to give it another try.

SJR · Accepted Answer · 2017-12-18 14:23:04Z

1

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

answered Dec 18, 2017 at 14:23

SJR

23.1k6 gold badges21 silver badges26 bronze badges

2 Comments

Jane_Tampa Over a year ago

I will give this a try. Where are you referencing worksheets? How do I know which worksheet I am working pulling from and which worksheet I am pulling into. Thank you for your help!

SJR Over a year ago

It uses sheet code names so just replace with usual names as in your code if that doesn't work.

PASUMPON V N · Accepted Answer · 2017-12-19 04:59:32Z

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

Collectives™ on Stack Overflow

VBA Copy and Paste random cells in a loop using VBA

3 Answers 3

1 Comment

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Related