# VBA - Filter Error



## CarlStephens (Dec 16, 2022)

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


----------



## HaHoBe (Dec 16, 2022)

Hi CarlStevens,

any reason to get the last filled cell from Column AP instead of BA?


```
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")

With wsData
  If .FilterMode Then .ShowAllData
  lr = .Cells(Rows.Count, "BA").End(xlUp).Row
  .Rows(1).AutoFilter Field:=53, Criteria1:="No"
  If .Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
    .Range("BD2:BJ" & lr).SpecialCells(xlCellTypeVisible).Copy
    wsDest.Range("D" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
'...
```
Ciao,
Holger


----------



## CarlStephens (Dec 16, 2022)

HaHoBe said:


> Hi CarlStevens,
> 
> any reason to get the last filled cell from Column AP instead of BA?
> 
> ...


Thank you, sir, that worked great. What would I need to add to the code, if a cell (F4) on the wsDest sheet was equal to 0, then the macro would not run and a message box would appear to say "No records to import."?

Thank you again.


----------



## HaHoBe (Dec 16, 2022)

Hi CarlStephens,

I would use an If clause and move the unprotect as well as the protect into the Else part like


```
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")

If wsDest.Range("D4").Value = 0 Then
  MsgBox "No records to import.", vbInformation, "Nothing to do"
Else
  wsData.Unprotect ("EPS")
  wsDest.Unprotect ("OT")
  With wsData
    If .FilterMode Then wsData.ShowAllData
    lr = .Cells(Rows.Count, "BA").End(xlUp).Row
    .Rows(1).AutoFilter Field:=53, Criteria1:="No"
    If .Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      .Range("BD2:BJ" & lr).SpecialCells(xlCellTypeVisible).Copy
      wsDest.Range("D" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
    End If
  End With
  wsDest.Protect ("OT")
  wsData.Protect ("EPS")
End If

Set wsDest = Nothing
Set wsData = Nothing

End Sub
```

Ciao,
Holger


----------



## CarlStephens (Dec 16, 2022)

HaHoBe said:


> Hi CarlStephens,
> 
> I would use an If clause and move the unprotect as well as the protect into the Else part like
> 
> ...


Sorry, I should post the whole code for you to see.

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")

With wsData
  If .FilterMode Then .ShowAllData
  lr = .Cells(Rows.Count, "BA").End(xlUp).Row
  .Rows(1).AutoFilter Field:=53, Criteria1:="No"
  If .Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
    .Range("BD2:BJ" & lr).SpecialCells(xlCellTypeVisible).Copy
    wsDest.Range("D" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
wsDest.Select
MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee records were found and copied to copied to this tab." & vbCrLf & _
"Next, update any crew that are showing as Scheduled in column C and send joining instructions if required.", vbInformation
Else
MsgBox "No new employee records found. Please check to see who has been 'Scheduled' per column C and add the new posting details, and who need joining instructions if required.", vbInformation
End If
    If .FilterMode Then .ShowAllData
wsDest.EnableAutoFilter = True
wsData.EnableAutoFilter = True
wsData.Protect Password:="EPS", UserInterfaceOnly:=True
wsDest.Protect Password:="OT", UserInterfaceOnly:=True
wsDest.Select
Application.GoTo (ActiveWorkbook.Sheets("OT").Range("C11"))

End With

End Sub


----------



## HaHoBe (Dec 16, 2022)

Hi CarlStephens,

please use code tags when displaying your prcedures according to How to Post Your VBA Code

Do you search for something like


```
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")

'/// check value cell D4 first
If wsDest.Range("D4").Value = 0 Then
  MsgBox "No records to import.", vbInformation, "Nothing to do"
Else
  With wsData
    .Unprotect ("EPS")
    wsDest.Unprotect ("OT")
    If .FilterMode Then .ShowAllData
    lr = .Cells(Rows.Count, "BA").End(xlUp).Row
    .Rows(1).AutoFilter Field:=53, Criteria1:="No"
    If .Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      .Range("BD2:BJ" & lr).SpecialCells(xlCellTypeVisible).Copy
      wsDest.Range("D" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
      MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & _
          " new employee records were found and copied to copied to this tab." & vbCrLf & _
          "Next, update any crew that are showing as Scheduled in column C and send joining instructions if required.", vbInformation
    Else
      MsgBox "No new employee records found. Please check to see who has been 'Scheduled' per column C " & _
          "and add the new posting details, and who need joining instructions if required.", vbInformation
    End If
    If .FilterMode Then .ShowAllData
    wsDest.EnableAutoFilter = True
    .EnableAutoFilter = True
    .Protect Password:="EPS", UserInterfaceOnly:=True
    wsDest.Protect Password:="OT", UserInterfaceOnly:=True
    Application.GoTo (wsDest.Range("C11"))
  End With
End If

Set wsDest = Nothing
Set wsData = Nothing
End Sub
```

Holger


----------



## CarlStephens (Dec 19, 2022)

HaHoBe said:


> Hi CarlStephens,
> 
> please use code tags when displaying your prcedures according to How to Post Your VBA Code
> 
> ...


Thank you so much, this worked perfectly.


----------

