Auto Copy Paste Special each time Excel auto generates new set of numbers.

[HelpHelpHelp]

Hi,

I hope to catch someone who loves working even-though X'mas is coming

My question is, is their a way to perform Copy Paste Special automatically?

As we know, Excel can generate a row (or more) of random numbers and auto re-generates new set of numbers when certain key are clicked. This is very useful for me.
Here is my problem, I need hundred of rows and it's laborious to just Paste Special (values and number formats) several hundred times.

So in sheet 1, let say A1:A10 I have up to 10 random numbers generated. I copy this row.
In sheet 2, I Paste Special this starting at A1. It then auto regenerates and I put the cursor to A2 and Paste Special again. Then A3, A4...A200. Could this action be automated?

I hope someone is magical here.

Thanks
HHH

[nankw83]

Hi HHH,

Try below code ...

Sub test()

Dim a(1 To 50, 1 To 10) 'change 50 to # rows needed & 10 to # of columns needed
   
For x = 1 To UBound(a)
   For y = 1 To UBound(a, 2)
      a(x, y) = WorksheetFunction.RandBetween(1, 100)
   Next
Next

Sheets("Sheet1").[A1].Resize(UBound(a), UBound(a, 2)) = a

End Sub