Hi all,
Hoping someone can help me crack the below logic that is to be applied to the below.
Essentially this code is scanning 3 columns and when it is going into a column it is filtering by anything not equal to #N/A. It then copies anything it finds and pastes it into another tab and then jumps into the next column and repeats this.
The bit I am stuck on - is that I didn't factor in what it should do if it filtered by not equal to #N/A and the filter came up as blank (ie no results or blank cell with nothing to copy). VBA throws an error when it gets this scenario.
Therefore, upon this instance, I would need the code to just jump onto the next column and do the check there and then the final column. If for any reason all 3 columns came up blank, then it should just clear the filters and move onto my next "Call method" as part of my overall VBA code module.
Seems like an IF Logic needs to be applied or some sort of Error Handling piece to add - but that's where I'm stuck!!
As always - many thanks in advance for your help Gurus!
My code:
Sub test1()
Sheets("Test Sheet").Select
Range("B17").AutoFilter Field:=2, Criteria1:="<>#N/A"
'-- If below is not true then skip to C17 otherwise continue with the copy
Range("B18:B300").SpecialCells(xlCellTypeVisible).Copy
Sheets("List").Select
Range("T3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Test Sheet").Select
Sheets("Test Sheet").ShowAllData
Range("C17").AutoFilter Field:=3, Criteria1:="<>#N/A"
'-- If below is not true then skip to D17 otherwise continue with the copy
Range("C18:C300").SpecialCells(xlCellTypeVisible).Copy
Sheets("List").Select
Range("T3").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Test Sheet").Select
Sheets("Test Sheet").ShowAllData
Range("D17").AutoFilter Field:=4, Criteria1:="<>#N/A"
'-- If below is not true then skip to take the action on the last comment noted at the end
Range("D18:D300").SpecialCells(xlCellTypeVisible).Copy
Sheets("List").Select
Range("T3").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Test Sheet").Select
Sheets("Test Sheet").ShowAllData
'-- If all 3 filters come up as blank, then skip this Sub() and continue with the outlined Call Method/Macros as part of the wider overall VBA code.
End Sub
Hoping someone can help me crack the below logic that is to be applied to the below.
Essentially this code is scanning 3 columns and when it is going into a column it is filtering by anything not equal to #N/A. It then copies anything it finds and pastes it into another tab and then jumps into the next column and repeats this.
The bit I am stuck on - is that I didn't factor in what it should do if it filtered by not equal to #N/A and the filter came up as blank (ie no results or blank cell with nothing to copy). VBA throws an error when it gets this scenario.
Therefore, upon this instance, I would need the code to just jump onto the next column and do the check there and then the final column. If for any reason all 3 columns came up blank, then it should just clear the filters and move onto my next "Call method" as part of my overall VBA code module.
Seems like an IF Logic needs to be applied or some sort of Error Handling piece to add - but that's where I'm stuck!!
As always - many thanks in advance for your help Gurus!
My code:
Sub test1()
Sheets("Test Sheet").Select
Range("B17").AutoFilter Field:=2, Criteria1:="<>#N/A"
'-- If below is not true then skip to C17 otherwise continue with the copy
Range("B18:B300").SpecialCells(xlCellTypeVisible).Copy
Sheets("List").Select
Range("T3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Test Sheet").Select
Sheets("Test Sheet").ShowAllData
Range("C17").AutoFilter Field:=3, Criteria1:="<>#N/A"
'-- If below is not true then skip to D17 otherwise continue with the copy
Range("C18:C300").SpecialCells(xlCellTypeVisible).Copy
Sheets("List").Select
Range("T3").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Test Sheet").Select
Sheets("Test Sheet").ShowAllData
Range("D17").AutoFilter Field:=4, Criteria1:="<>#N/A"
'-- If below is not true then skip to take the action on the last comment noted at the end
Range("D18:D300").SpecialCells(xlCellTypeVisible).Copy
Sheets("List").Select
Range("T3").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Sheets("Test Sheet").Select
Sheets("Test Sheet").ShowAllData
'-- If all 3 filters come up as blank, then skip this Sub() and continue with the outlined Call Method/Macros as part of the wider overall VBA code.
End Sub