I need help to be able to get random data from another worksheet with specific conditions:
If i click a button/run a macro, I should get 4 random samples for all rows that has "AA"
, 1 random sample for all rows that has "BB"
, 1 random sample for all rows that has "CC"
, 3 random samples for all rows that has "DD"
, and 1 random sample for all rows that has "EE"
...
... FROM rawdata.xlsx "Sheet1"
sheet and paste it to tool.xlsm "Random Sample"
sheet.
All should happen in one click.
This is my code so far. I can only get specific amount of random data from rawdata.xlsx. I hope someone can help me. thanks in advance
Sub CopyRandomRows()
Sheets("Random Sample").Select
Cells.Select
Range("C14").Activate
Selection.Delete Shift:=xlUp
Windows("rawdata.xlsx").Activate
Rows("1:1").Select
Selection.Copy
Application.CutCopyMode = False
Selection.Copy
Windows("tool.xlsm").Activate
Sheets("Random Sample").Select
Rows("1:1").Select
ActiveSheet.Paste
Dim source As Range, target As Range, randCount&, data(), value, r&, rr&, c&
'this defines the source to take the data
With Workbooks("rawdata.xlsx").Worksheets("Sheet1")
Set source = .Range("A1:" & .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Address)
End With
'this defines the target to paste the data
Set target = Workbooks("tool.xlsm").Worksheets("Random Sample").Range("A2")
'this defines the number of rows to generate based on the input in textbox
randCount = 20
'this loads the data in an array
data = source.value
'this shuffles the rows
For r = 1 To randCount
rr = 1 + Math.Round(VBA.rnd * (UBound(data) - 1))
For c = 1 To UBound(data, 2)
value = data(r, c)
data(r, c) = data(rr, c)
data(rr, c) = value
Next
Next
'this writes the data to the target
target.Resize(randCount, UBound(data, 2)) = data
End Sub
Aucun commentaire:
Enregistrer un commentaire