VBA - Range of object _ Global failed

[_Br_10]

Hi everyone!
Yesterday i run this code and was all ok. But today i don't know why appear this error 1004.

The error is in the line of the range.

The code is:




Worksheets("Encomendas_em_aberto").Range("A2:I" & lastRowZabr).ClearContents
Worksheets("Zplan_producao").Select
ActiveSheet.ListObjects("Z_Plan_Oficial").Range.AutoFilter Field:=2, _
Criteria1:="=ABER"
Range("A:A,B:B,E:E,F:F,G:G,I:I,J:J,K:K,R:R").Select ----> ERROR
Selection.Copy
Sheets("Encomendas_em_aberto").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False


How resolve this?

[p45cal]

A guess:
If the code is in a sheet's code-module and that sheet is not the active sheet, you will get that error.
This is because an unqualified reference like Range("A:A,B:B,E:E,F:F,G:G,I:I,J:J,K:K,R:R") in a sheet's code-module always refers to that sheet. If it's not the active sheet it can't select that range.
To avoid the error:
Qualify the reference: Worksheets("Zplan_producao").Range("A:A,B:B,E:E,F:F,G:G,I:I,J:J,K:K,R:R") or Sheet3.Range("A:A,B:B,E:E,F:F,G:G,I:I,J:J,K:K,R:R") (here I've guessed the code name for that sheet).
Avoid selecting altogether, so instead of:
Worksheets("Zplan_producao").Select
ActiveSheet.ListObjects("Z_Plan_Oficial").Range.AutoFilter Field:=2, Criteria1:="=ABER"
use:
Worksheets("Zplan_producao").ListObjects("Z_Plan_Oficial").Range.AutoFilter Field:=2, Criteria1:="=ABER"

or instead of:
ActiveSheet.ListObjects("Z_Plan_Oficial").Range.AutoFilter Field:=2, Criteria1:="=ABER"
Range("A:A,B:B,E:E,F:F,G:G,I:I,J:J,K:K,R:R").Select ' ----> ERROR
Selection.Copy
Sheets("Encomendas_em_aberto").Select
Range("A1").Select
ActiveSheet.Paste
try:
With Worksheets("Zplan_producao")
.ListObjects("Z_Plan_Oficial").Range.AutoFilter Field:=2, Criteria1:="=ABER"
.Range("A:A,B:B,E:E,F:F,G:G,I:I,J:J,K:K,R:R").Copy Sheets("Encomendas_em_aberto").Range("A1")
End With
Does the code have to be in a sheet's code-module?

(Now you're going to tell me the code's not in a sheet's code-module! :()

[_Br_10]

Nice! It resulted. Thank you so much.

[snb]

I'd prefer:


With Worksheets("Zplan_producao")
.ListObjects(1).Range.AutoFilter 2, "ABER"
.Range("A:B,E:G,I:K,R:R").Copy Sheets("Encomendas_em_aberto").cells(1)
End With