limit the number of rows copied over

[rickblunt]

Hello, I am using the following code to sort data in a WS and copy it over to another WS. It all works just fine but I was thinking that I would like to limit it the first "X" numbers of rows so that my second WS doesn't get too big - lets say the first 5 entries. So I am thinking that I want to put a counter on each time it copies, and limit that number in End If statement. Is that the way to do it? I tried a couple of things but I can't tell if I am using the wrong syntax or if I am just wrong in the first place - I always appreciate your input.

I really try to do this on my own before I bug you all with it .

Sub CopyEpipro()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim setpoint As Integer
    
    Set Source = ActiveWorkbook.Worksheets("EPIPRO")
                     
    Set Target = ActiveWorkbook.Worksheets("Export")
                   
    setpoint = ActiveWorkbook.Worksheets("Coding").Range("C2")
    
    j = 45     ' Start copying to row # in target sheet
    For Each c In Source.Range("H5:H120")   ' The column for the micron reading
        If c >= setpoint Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

 

[johnnyL]

@rickblunt What do you want to do after you have copied the 5 rows? Nothing else?

 

[Peter_SSs]

Here is one way

Sub CopyEpipro()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim setpoint As Integer
    Dim StopAt As Long
    
    Const NumToCopy As Long = 5
   
    Set Source = ActiveWorkbook.Worksheets("EPIPRO")
                    
    Set Target = ActiveWorkbook.Worksheets("Export")
                  
    setpoint = ActiveWorkbook.Worksheets("Coding").Range("C2")
   
    j = 45     ' Start copying to row # in target sheet
    StopAt = j + NumToCopy
    For Each c In Source.Range("H5:H120")   ' The column for the micron reading
        If c >= setpoint Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
        If j = StopAt Then Exit For
    Next c
End Sub

 

[rickblunt]

@rickblunt What do you want to do after you have copied the 5 rows? Nothing else?

yup, that's all - just copy the rows. There is more subs that I run in addition to this one, but this one only does that.

 

[rickblunt]

Here is one way

Sub CopyEpipro()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim setpoint As Integer
    Dim StopAt As Long
    
    Const NumToCopy As Long = 5
  
    Set Source = ActiveWorkbook.Worksheets("EPIPRO")
                   
    Set Target = ActiveWorkbook.Worksheets("Export")
                 
    setpoint = ActiveWorkbook.Worksheets("Coding").Range("C2")
  
    j = 45     ' Start copying to row # in target sheet
    StopAt = j + NumToCopy
    For Each c In Source.Range("H5:H120")   ' The column for the micron reading
        If c >= setpoint Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
        If j = StopAt Then Exit For
    Next c
End Sub

Thanks Peter - this worked perfectly. So you declared a constant and assigned it the limit, and then created a "counter" by making the variable "StopAt" do the counting; then count up the "j's" as it were. I think I get it (sort of, lol). Thank you for another training session.

 

[Peter_SSs]

You're welcome. Glad to help.