Hi,
I have a code where I'm simply filtering, copying and pasting based on the filtered selection. However, if the criteria filter does not exist, my code gets a Debug error.
Here is my code:
Code
Sub ServiceExportFormat()
Dim LastRow As Long
Application.ScreenUpdating = False
Sheets("Services Export").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
LastRow = Selection.Count + 1
'Create Unique Key
With Sheets("Services Export")
.Range("A2").Formula = "=C2&""-""&N2&""-""&Z2&""-""&AA2"
.Range("A2").AutoFill Destination:=.Range("A2:A" & LastRow)
End With
'Paste ServicesExport DSP
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=DSP"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (DSP)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Paste ServicesExport REC
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=REC"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (REC)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Paste ServicesExport H
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=H"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (H)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Paste ServicesExport CSC
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=CSC"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (CSC)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Paste ServicesExport LR
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=LR"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (LR)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Paste ServicesExport MNT
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=MNT"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (MNT)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'Paste ServicesExport R
Worksheets("Services Export").Range("A1").AutoFilter _
Field:=21, Criteria1:="=R"
Sheets("Services Export").Select
Range("A1").Select
ActiveSheet.Range(Selection, Cells(Selection.End(xlDown).Row, Selection.End(xlToRight).Column)).Select
Selection.Copy
Sheets("Services Export (R)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Display More
Any thoughts?
This is cross posted at: https://www.mrexcel.com/forum/…ound-instead-looping.html
Thank you!