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

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

Thanks! (blush...)

Greg


Dave Peterson wrote:
> 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 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").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