Help with a macro

Copper Contributor

Help with a macro

Hi - I am trying to create a macro to do the following:

Scan column for a number, if that number is 100, then copy and paste that row 50 times in the 2nd sheet.

Then, it changes if the number is 10, then copy and paste that row 3 times in the 2nd sheet.  Each of the copy and pastes have to go to the bottom row of the 2nd sheet before pasting.

Can someone assist?  It's been a while since I have done a macro and it doesn't seem to be tracking my actions as I am used to.

Thank you

Stacy

3 Replies

Re: Help with a macro

Here you go. The comments indicate the bits that you have to edit for your workbook:

``````Sub CopyRows()
Const c = "H"    ' the column to look at
Dim wss As Worksheet
Dim wst As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Application.ScreenUpdating = False
Set wss = Worksheets("Sheet1")    ' the source sheet
Set wst = Worksheets("Sheet2")    ' the target sheet
t = wst.Cells(wst.Rows.Count, c).End(xlUp).Row + 1
m = wss.Cells(wss.Rows.Count, c).End(xlUp).Row
For s = 1 To m
Select Case wss.Cells(s, c).Value
Case 100
wss.Cells(s, c).EntireRow.Copy Destination:=wst.Cells(t, 1).Resize(50)
t = t + 50
Case 10
wss.Cells(s, c).EntireRow.Copy Destination:=wst.Cells(t, 1).Resize(3)
t = t + 3
End Select
Next s
Application.ScreenUpdating = True
End Sub``````

Re: Help with a macro

thank you very much!! that worked great!
Any chance you can help me randomly mix the rows I created. I think I can run a random number generator. Is that just =RAND?

Re: Help with a macro

Yes - you could add the formula =RAND() in an empty column, then sort on that column.