Hi all,
Can someone help with the below?
I want to cut a row of data and paste it into a separate sheet based on the filter. Here is the code I have so far but an error appears on the highlighted line:
Thanks!
Can someone help with the below?
I want to cut a row of data and paste it into a separate sheet based on the filter. Here is the code I have so far but an error appears on the highlighted line:
Rich (BB code):
Sub completecand()
Dim FilterString As String
'
With Sheets("HR Advice & Admin")
FilterString = Sheets("Pre-employment Clearances").Range("C4").Value
.Range("$A$1:$AS$286").AutoFilter Field:=1, Criteria1:=FilterString
Intersect(.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible), .Range("A:AQ")).Cut
On Error GoTo NoVisible
Sheets("Completed").Range("A2:A" & Rows.Count).SpecialCells(xlVisible)(1).PasteSpecial xlPasteValues
On Error GoTo 0
.AutoFilterMode = False
End With
Sheets("Offer Received").Select
Application.CutCopyMode = False
Exit Sub
NoVisible:
Sheets("Pre-employment Clearances").Range("F4").Copy
Sheets("Completed").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Sheets("HR Advice & Admin").AutoFilterMode = False
Sheets("Pre-employment Clearances").Select
Application.CutCopyMode = False
End Sub
Thanks!