Autofill formula on non-Active sheet

[Greg Lovern]

Is it possible to autofill formulas on a worksheet other than the
active sheet?

For example, this works fine if Sheet1 is active, but returns an error
if not:

ThisWorkbook.Worksheets("Sheet1").Range("A1").AutoFill
Range("A1:A10000"), xlFillDefault

I need to autofill formulas that a user will enter, and at design time
I have no knowledge of what the formula will be. It would be a lot of
work to parse out the various parts of a large, complex formula so that
absolute references & relative references are each autofilled
correctly.

Do I have to do something like this, which helps but isn't ideal?

Dim fIsNotActiveSheet As Boolean
Dim shAutoFill As Worksheet
Dim shActive As Worksheet

Application.ScreenUpdating = False

Set shAutoFill = ThisWorkbook.Worksheets("Sheet1")

If shAutoFill.Name <> ActiveSheet.Name Then
fIsNotActiveSheet = True
Set shActive = ActiveSheet
shAutoFill.Activate
End If

shAutoFill.Range("A1").AutoFill Range("A1:A10000"), xlFillDefault

If fIsNotActiveSheet Then
shActive.Activate
End If

Application.ScreenUpdating = True




Thanks,

Greg

 

[Dave Peterson]

Fully qualify that range:

ThisWorkbook.Worksheets("Sheet1").Range("A1").AutoFill _
Thisworkbook.worksheets("Sheet1").Range("A1:A10000"), xlFillDefault

Or

with ThisWorkbook.Worksheets("Sheet1")
.Range("A1").AutoFill _
.Range("A1:A10000"), xlFillDefault
end with

 

[Greg Lovern]

Thanks! (blush...)

Greg

Fully qualify that range:

ThisWorkbook.Worksheets("Sheet1").Range("A1").AutoFill _
Thisworkbook.worksheets("Sheet1").Range("A1:A10000"), xlFillDefault

Or

with ThisWorkbook.Worksheets("Sheet1")
.Range("A1").AutoFill _
.Range("A1:A10000"), xlFillDefault
end with