0

I have the following code which returns an error: Invalid Next control variable reference. Can anybody point to where I am wrong and how do I Improvise? The code is to extract rows based on a certain key word from cell A2 onwards 

Sub Macro1()
    Dim DVariable As Date
    Dim RngFind As Range
    Dim MonthNo, YearNo As Integer
    Dim StartDate, EndDate As Date
    Dim PasteCell As Range
    Dim M As Long, i As Long, j As Long

    Application.DisplayAlerts = False
    Sheets("Report").Select
    Set ws1 = ThisWorkbook.Sheets.Add(After:= _
            ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws1.Name = "NEW"
    Sheets("Macro").Select
    For M = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("Macro").Select
            With Sheets("By Trader")
    'loop column N until last cell with value (not entire column)
                For Each Cell In .Range("N1:N" & .Cells(.Rows.Count, "N").End(xlUp).Row)
                    If Cell.Value = M Then
             ' Copy>>Paste in 1-line (no need to use Select)
                    .Rows(Cell.Row).Copy Destination:=Sheets("NEW").Rows(Cell.Row)
                    End If
                Next
            End With
     Next
End Sub
Zam
  • 2,880
  • 1
  • 18
  • 33
user12151877
  • 41
  • 5
  • A few notes: You should declare `ws1` as a worksheet `Dim ws1 As Worksheet`. You should try to avoid using `.Select` as is described [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It doesn't seem like you need the `.Select` statements in this case. Have you tried specifying your `Next` statements like this: `Next Cell` and `Next M`? You should also declare `Cell` as a `Range`. – riskypenguin Oct 07 '19 at 06:49
  • the shown code can't trigger the error you write about: update the post with the _real_ code or error – DisplayName Oct 07 '19 at 08:00
  • its the real code:( – user12151877 Oct 07 '19 at 08:01
  • `Cells(Rows.Count, "A").End(xlUp).Row` is not working. Cells works with numbers, so you should go for `Cells(Rows.Count, 1).End(xlUp).Row` or `Range("A" & Rows.Count).End(xlUp).Row` Same issue on `.Cells(.Rows.Count, "N")` – David García Bodego Oct 07 '19 at 12:59

0 Answers0