excel - VBA: Copy and paste entire row based on if then statement / loop and push to 3 new sheets

Question

I'm new to VBA programming and am having an absolutely insanely difficult time understanding how to put these concepts together.

My goal is to select multiple rows (that have multiple strings in each column) based on criteria, such as "If the values in column h are less than 50%, copy entire rows with such criteria and move to a new sheet called, 'less than 50'. If the values in column h are over 70%, copy entire rows and move to a new sheet called 'over 70%'. if values in column h are in between 50-70%, move to a sheet called in between."

I've tried for weeks looking at tutorials and other information and just can't figure it out.

None of this works but this is pretty much the things I've learned over the last couple of weeks, and where I'm trying to go with my code.

Sub cool() 
    i As Integer 
    For i = 2 To Number 
        If Cells(i, 3).Value < 50 Then 
            activecells.Rows().EntireRow.Copy 
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = under50
            Worksheets("under50").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        End If
    Next i
End Sub

Answer 1

Here is a major rewrite of your code, that will hopefully do what you want. I have included some comments explaining what I changed, but once I got to the If statement it became too different to the original code to continue adding many comments.

Sub cool()
    'Need "Dim"
    'Recommend "Long" rather than "Integer" for referring to rows and columns
    'i As Integer
    Dim i As Long
    'Declare "Number"
    Dim Number As Long
    'Declare a variable to refer to the sheet you are going to copy from
    Dim wsSrc As Worksheet
    Set wsSrc = ActiveSheet
    'Declare a variable to refer to the sheet you are going to copy to
    Dim wsDest As Worksheet
    'Declare three other worksheet variables for the three potential destinations
    Dim wsLessThan50 As Worksheet
    Dim wsInBetween As Worksheet
    Dim wsOver70 As Worksheet
    'Create the three sheets - do this once rather than in the loop
    Set wsLessThan50 = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    Set wsInBetween = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    Set wsOver70 = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    'Assign the worksheet names
    wsLessThan50.Name = "less than 50"
    wsInBetween.Name = "in between"
    wsOver70.Name = "over 70"

    'Determine last row in source sheet
    Number = wsSrc.Cells(wsSrc.Rows.Count, "C").End(xlUp).Row

    For i = 2 To Number
        'Always qualify "Cells" to specify which sheet you are referring to
        '(It defaults to ActiveSheet, but leaving it out when ActiveSheet is
        ' NOT intended to be used leads to about 25% of the problems on SO,
        ' so get into the habit of always specifying it.)
        'Question says column H, but you are referring to column C
        '50% is equal to 0.5, not 50
        'If Cells(i, 3).Value < 50 Then

        'Determine which destination sheet to use
        If wsSrc.Cells(i, "C").Value < 0.5 Then
            Set wsDest = wsLessThan50
        ElseIf wsSrc.Cells(i, "C").Value > 0.7 Then
            Set wsDest = wsOver70
        Else
            Set wsDest = wsInBetween
        End If

        'Copy the current row from the source sheet to the next available row on the
        'destination sheet
        With wsDest
            'This next line will start placing things in the destination sheets starting
            'from row 2.  I could say that that is intentional (so that you have a row
            'that you can use for headings) but it is really because I am too lazy to
            'include the extra lines to check whether row 1 is empty and, if so, copy to
            'row 1, else copy to the next available row.
            wsSrc.Rows(i).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
    Next i
End Sub