I am trying to filter data in one sheet depending on a variable in another. But even though I have the same formatting in both sheets it will not recognize the number. I have tried everything from pasting values, to pasting the same formats in both areas, using the F2 option, but nothing seems to work. My single value is in Sheet1 B2. The rest of the data I'm filtering is in Exceptions2 with the filter criteria residing in column A. I have headers in row 1.
Code that is running the process is below:
Code that is running the process is below:
Code:
Sub Recall_BT()
Sheets("DataEntry").Unprotect
Sheets("Exceptions2").Visible = True
Dim rr As Range, r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim rCell As Variant
rCell = Sheets("Sheet1").Range("g3")
Dim sh As Worksheet
Dim wsCopyTo As Worksheet
Application.ScreenUpdating = False
Set sh = Worksheets("Exceptions2")
sh.Select
sh.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=rCell
Set rr = ActiveSheet.AutoFilter.Range
Set rr = rr.Offset(1, 0).Resize(rr.Rows.Count - 1, 1)
On Error Resume Next
Set r1 = rr.SpecialCells(xlVisible)
On Error GoTo 0
If r1 Is Nothing Then
MsgBox "No visible cells"
Exit Sub
End If
Set r2 = Intersect(r1.EntireRow, sh.Range("F:L").EntireColumn)
Set r3 = Intersect(r1.EntireRow, sh.Range("N:W").EntireColumn)
Set wsCopyTo = Worksheets("BackTemp1")
r2.Copy
wsCopyTo.Range("I14").PasteSpecial _
Paste:=xlPasteValues
r3.Copy
wsCopyTo.Range("Q14").PasteSpecial _
Paste:=xlPasteValues
r2.EntireRow.Delete
Sheets("Exceptions2").Visible = False
Sheets("DataEntry").Protect
Application.ScreenUpdating = True
End Sub