Forum Discussion
VBA Error (but only on one sheet): Object variable or With block variable not set
You'd get this error if the range doesn't have filter turned on, so that ActiveSheet.AutoFilter is undefined. Add the following lines at the beginning of the macro:
If Not ActiveSheet.AutoFilterMode Then Range("A1").AutoFilter End If
You'd get this error if the range doesn't have filter turned on, so that ActiveSheet.AutoFilter is undefined. Add the following lines at the beginning of the macro:
If Not ActiveSheet.AutoFilterMode Then
Range("A1").AutoFilter
End If
HansVogelaar Thank you!! This completely solved the problem. When you say "range doesn't have the filter turned on," what do you mean by that? I will keep this in mind going forward so I don't make the same mistake again.
On a sheet where the original code worked, you'll see filter arrows in the cells in row 1:
If you don't see those arrows, Filter hasn't been turned on and the code will fail. The lines that I suggested will turn on the filter arrows (it's the equivalent of selecting Sort & Filter > Filter on the Home tab of the ribbon)
HansVogelaar Thank you for explaining! That's interesting, I actually do have the filters turned on in both tabs of the spreadsheet. So I'm not sure why it wasn't working... but either way, your solution fixed it! Thanks again, I appreciate it.
Edit - or maybe your code fixed it *facepalm* Anyway, I'll pay closer attention to this going forward!