Autofill formula on non-Active sheet
 

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").Auto Fill
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

Autofill formula on non-Active sheet
 

Fully qualify that range:

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

Or

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



Greg Lovern wrote:

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").Auto Fill
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

Autofill formula on non-Active sheet
 

Thanks! (blush...)

Greg


Dave Peterson wrote:
Fully qualify that range:

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

Or

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



Greg Lovern wrote:

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").Auto Fill
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