VBA code to move row to new spreadsheet
I want to have this code wopy over all rows with a "Y" in column K to a new spreadsheet. I have this so far but it will only bring over the first row if it is marked. I need to get this to move over all rows marked even if the first row is not marked. Any help is appreciated. Thanks
Code:
Dim colLetter As String, SavePath As String
Dim lastValue As String
Dim wb As Workbook
Dim lng As Long
Dim currentRow As Long
colLetter = "K"
SavePath = ThisWorkbook.Path
With ThisWorkbook.Worksheets(1)
.Cells.AutoFilter field:=.Cells(1, colLetter).Column, Criteria1:="Y"
lng = .Cells(.Rows.Count, colLetter).End(xlUp).Row
Set wb = Application.Workbooks.Add(xlWorksheet)
.Rows(1 & ":" & lng).Copy wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)
.AutoFilterMode = False
