CarlStephens
Board Regular
- Joined
- Sep 25, 2020
- Messages
- 128
- Office Version
- 2016
- Platform
- Windows
Hello Wonderful People,
I have the below code, which filters a column by the cells that have a "No" in them, and then copies the cells in "BD2:BJ" and then pastes the data to another sheet, which is working all well, with the exception that the last row in "BD2:BJ" of the filtered list is not copied across, even though the filter shows "No". Any ideas on this will be appreciated as I cannot see what the issue is. Thank you.
Sub FindNewHires()
'
Dim wsData As Worksheet
Dim wsDest As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set wsData = Worksheets("EPS")
Set wsDest = Worksheets("OT")
wsData.Unprotect ("EPS")
wsDest.Unprotect ("OT")
lr = wsData.Cells(Rows.Count, "AP").End(xlUp).Row
If wsData.FilterMode Then wsData.ShowAllData
With wsData.Rows(1)
.AutoFilter Field:=53, Criteria1:="No"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("BD2:BJ" & lr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("D" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
I have the below code, which filters a column by the cells that have a "No" in them, and then copies the cells in "BD2:BJ" and then pastes the data to another sheet, which is working all well, with the exception that the last row in "BD2:BJ" of the filtered list is not copied across, even though the filter shows "No". Any ideas on this will be appreciated as I cannot see what the issue is. Thank you.
Sub FindNewHires()
'
Dim wsData As Worksheet
Dim wsDest As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set wsData = Worksheets("EPS")
Set wsDest = Worksheets("OT")
wsData.Unprotect ("EPS")
wsDest.Unprotect ("OT")
lr = wsData.Cells(Rows.Count, "AP").End(xlUp).Row
If wsData.FilterMode Then wsData.ShowAllData
With wsData.Rows(1)
.AutoFilter Field:=53, Criteria1:="No"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("BD2:BJ" & lr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("D" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues