Having troubles with XlFillDefault

[Clevelands]

Hey guys I just started learning how to use VBA.
Currently I'm stuck with Run Time error 1004 and it says"Application-defined or object-defined error".
Here's the code:
Worksheets("Data").Range("A9:C9").AutoFill Destination:=Worksheets("Data").Range(Cells(9, 1), Cells(35, 3)), Type:=xlFillDefault
The weird thing is when I'm viewing on the "Data" Worksheets, it runs without any error.
However, I tried to run the code when I'm viewing my other worksheet and it gave me an error.



I tried to run another code and it can run without me being on the "Data" Worksheet. Here's the code below:
Sheets("Data").Range("A9:C9").AutoFill Destination:=Sheets("Data").Range("A9:C35"), Type:=xlFillDefault

Anyone knows the reason why? I'm sorry if I didn't clarify my problems clearly.

[SamT]

I've never tried to fill multiple columns. Nothing in any help file hints it was possible

[Fluff]

The problem is that you haven't fully qualified the ranges in the destination, try

With Worksheets("Data")
   .Range("A9:C9").AutoFill Destination:=.Range(.Cells(9, 1), .Cells(35, 3)), Type:=xlFillDefault
End With

[Clevelands]

OMG thank you so much it works!

[Fluff]

You're welcome & thanks for the feedback