Writing formulas to multiple sheets at one time

[eAlchemist]

I'm trying to write formulas to the same cell of multiple worksheets, but having troubles.

I do this:
SheetsArray "GIRLS", "MENS", "MATERNITY", "WOMENS", "BOYS", "ACCESS", "BABY", "LARGE", "MISC")).Select

And then this:
Selection.Range(formulaAddress).FormulaArray = ActiveWorkbook.Sheets("ArrayFormulas").Cells(rowPosition, 2).Formula

But, this only writes the formula to the active worksheet. When I record the action, this is basically what it gives me.
Can someone suggest something?

Thanks,
Chris

 

[eAlchemist]

BTW...

I can make this work with a For Each Sheet loop, but that's slow and I'm hoping to do it faster.

Thanks,
Chris

 

[VBAjedi]

The loop approach should be extremely fast if you don't select anything. The Select statement is probably what is slowing your code down.

Post the loop and we can help you eliminate the selects.

VBAjedi

 

[eAlchemist]

Here is the code. This took just over 15 min. to run for 10 sheets.

Thanks,
Chris


For Each Sheet In ActiveWorkbook.Sheets

If Sheet.Name = "TOTAL" Or Sheet.Name = "ArrayFormulas" Or Sheet.Name = "Controls" Or Sheet.Name = "RawData" Then
GoTo SkipSheet
End If


For rowPosition = 1 To 1824

formulaAddress = ActiveWorkbook.Sheets("ArrayFormulas").Cells(rowPosition, 1).Value
Sheet.Range(formulaAddress).FormulaArray = ActiveWorkbook.Sheets("ArrayFormulas").Cells(rowPosition, 2).Formula

Next rowPosition

SkipSheet:

Next Sheet

 

[PHV]

You may try this:
Application.ScreenUpdating = False
[...]
With ActiveWorkbook.Sheets("ArrayFormulas")
For rowPosition = 1 To 1824
formulaAddress = .Cells(rowPosition, 1).Value
Sheet.Range(formulaAddress).FormulaArray = .Cells(rowPosition, 2).Formula
Next rowPosition
End With
[...]
Application.ScreenUpdating = True

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eAlchemist]

So does this all mean that you can't write the formulas to more than one tab at a time?

Chris

 

[VBAjedi]

Can't" is a pretty strong word that I try to stay away from (especially around here! ), but I will say that I've not seen it done before.

I'm just not that motivated to figure it out because I'm not convinced that it has any advantage over more conventional approaches.

How long did PHV's code take to run?

VBAjedi