So, I have written this subroutine code in VBA which basically looks for data (looks for dates - 2004 to 2008) on one worksheet (called Panel Data) , if it finds the said data, it cuts & pastes the whole row onto a new worksheet (called VBA). So, the problem is - I have more than 10,000 data points so I can't keep pressing run 10,000 times and I would like the code to run until it cuts & pastes ALL the data points that match my criteria onto the new worksheet. How do you suggest I do this? What additions should I make to my code so that it runs itself till all the relevant data has been transferred to the VBA worksheet? I have tried a while loop but I don't know where/how exactly should I incorporate it into my code. I could have tried more but this is urgent. Hence my post here.
Sub Riskcon()
Dim LR As Long
Range("A2").EntireRow.Insert Shift:=xlDown
LR = Sheets("Panel Data").Cells(Rows.Count, "C").End(xlUp).Row
LR1 = Sheets("VBA").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Panel Data").Range("C2:C" & LR)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="2004", _
Operator:=xlOr, Criteria2:="2005"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets(" VBA").Range("A" & LR1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
Thank you very much!
Bookmarks