VBA code to move row to new spreadsheet

[cdurfey]

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


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

[patel]

.Rows(2 & ":" & lng).Copy wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp)
.AutoFilterMode = False



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533)
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468 (https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468)
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411 (https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411)
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384 (https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378)
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305 (https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035)
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889 (https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[cdurfey]

That didn't work. All I am getting is a blank new workbook except when all of the "Y" are at the top in order. As soon as it hits an "N" it stops pulling over.

[Rick Rothstein]

Does this code do what you want (note that I removed some of the variables you declared which were not needed for this code, so if you need them later, you will have to Dim them again)...

Dim ColLetter As String, SavePath As String
Dim VisibleRows As Range, WS As Worksheet, wbOld As Workbook, wbNew As Workbook
ColLetter = "K"
Set wbOld = ThisWorkbook
Set wbNew = Application.Workbooks.Add(xlWorksheet)
Set WS = wbOld.Worksheets(1)
WS.Rows(1).Insert
WS.Cells.AutoFilter field:=WS.Cells(1, ColLetter).Column, Criteria1:="Y"
WS.AutoFilter.Range.SpecialCells(xlVisible).Copy wbNew.Worksheets(1).Range("A1")
WS.AutoFilterMode = False
WS.Rows(1).Delete

[cdurfey]

This works great except it does not bring over to column headers. Can you help with that? I have almost destroyed the macro. Thanks

[Rick Rothstein]

This works great except it does not bring over to column headers. Can you help with that? I have almost destroyed the macro. Thanks
Not sure what you mean by "I have almost destroyed the macro", but this modified version of what I posted earlier should do what I think you want...

Dim ColLetter As String, SavePath As String
Dim VisibleRows As Range, WS As Worksheet, wbOld As Workbook, wbNew As Workbook
ColLetter = "K"
Set wbOld = ThisWorkbook
Set wbNew = Application.Workbooks.Add(xlWorksheet)
Set WS = wbOld.Worksheets(1)
WS.Cells.AutoFilter field:=WS.Cells(1, ColLetter).Column, Criteria1:="Y"
WS.AutoFilter.Range.SpecialCells(xlVisible).Copy wbNew.Worksheets(1).Range("A1")
WS.AutoFilterMode = False

[cdurfey]

That works perfect. Thanks