VBA: Different worksheets, same cell ranges?

Question

I am using code that returns "range of object_global failed" with the second part of the code below (3rd sheet code).

Would use the same named ranges, but on different sheets, as I did below, causes this?

Worksheets(2).Range("LTSDI").AutoFill Destination:=Range("LTSDI:LTEDI"), Type:=xlFillDefault

Worksheets(3).Range("LTSDI").AutoFill Destination:=Range("LTSDI:LTEDI"), Type:=xlFillDefault

Thanks for any feedback!

Answer 1

To be clearer than the comments suggested above, named ranges work as follows.

Named ranges can have 2 scopes:

• Workbook level
• Sheet level

Excel , . , Excel .

, :

• myRange= Sheet1! $A $1 :
• myRange= Sheet2! $A $1 : 2

:

• myRange= Sheet1! $A $1 :
• myRange= Sheet2! $A $1 :

, , :

: =MyRange

, , : =MyRange

, : ='Sheet1!myRange'

, , , , .

Answer 2

,

'1004':
Range

.

With Worksheets(2)
    .Range("LTSDI").AutoFill Destination:=.Range("LTSDI:LTEDI"), Type:=xlFillDefault
End With

With Worksheets(3)
    .Range("LTSDI").AutoFill Destination:=.Range("LTSDI:LTEDI"), Type:=xlFillDefault
End With

With... End With statement. Range (aka full stop .), , With... End .

Range.FillDown, With... End With, .

Worksheets(2).Range("LTSDI:LTEDI").FillDown
Worksheets(3).Range("LTSDI:LTEDI").FillDown

, , , .

Worksheets(2).Range("LTSDI").AutoFill Destination:=Worksheets(2).Range("LTSDI:LTEDI"), Type:=xlFillDefault
Worksheets(3).Range("LTSDI").AutoFill Destination:=Worksheets(3).Range("LTSDI:LTEDI"), Type:=xlFillDefault