Help with a macro

Question

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

hansvogelaar · Answer

STselekis&nbsp;
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
&nbsp;

stselekis · Answer

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?

hansvogelaar · Answer

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

Forum Discussion

Help with a macro

3 Replies

Resources