Copying Formulae down across multiple sheets

[VWadsworth]

Hi I've written a macro that automatically updates data in Cells C5:C33 based on Cells B5:B33 (and the contents of A3). The updated information comes from another sheet, and is pulled in using a formula which is then supposed to be copied into cells C5:C33 across all the sheets in the array. I've had it working fine for a while but it seems to have stopped. Can anyone give me some pointers/let me know if you can see a reason why it's not working. From what I can tell it does copy the formula into cell C5 and works but then fails to copy it down save on the first sheet.

Sub APBBalUpdate()
'
' APBBalUpdate Macro
' Updates all recs save the first. Uses Cell B5:C33
'
' Keyboard Shortcut: Ctrl+u
'
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Sheet1", "Sheet2", _
"Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8", "Sheet9", "Sheet10", _
"Sheet11", "Sheet12", "Sheet13", "Sheet14", _
"Sheet15", "Sheet15", "Sheet16", "Sheet17", _
"Sheet18", "Sheet19", "Sheet20", "Sheet21", _
"Sheet22", "Sheet23", "Sheet24")).Select
Sheets("Sheet1").Activate
Sheets(Array("Sheet25", "Sheet26", "Sheet27", "Sheet28", _
"Sheet29", "Sheet30", "Sheet31", "Sheet32", _
"Sheet33", "Sheet34", "Sheet35", "Sheet36", "Sheet37", "Sheet38", _
"Sheet39", "Sheet40")).Select Replace:=False
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Range("C5").Select
ActiveCell.FormulaR1C1 = "=IF(RC2="""","""",IF(ISERROR(FIND(""Rent"",R3C1)),SUMIFS('APB Bank Balances.xls'!R1C8:R1000C8,'APB Bank Balances.xls'!R1C4:R1000C4,RC2,'APB Bank Balances.xls'!R1C9:R1000C9,21),SUMIFS('APB Bank Balances.xls'!R1C8:R1000C8,'APB Bank Balances.xls'!R1C4:R1000C4,RC2,'APB Bank Balances.xls'!R1C9:R1000C9,11)))"
Range("C5").Select
Selection.Copy
Range("C5:C33").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1:F1").Select
Sheets("Sheet41").Activate
Sheets("Sheet1").Activate
End Sub

[ragulduy]

Maybe try something like this instead?

Please Login or Register  to view this content.

[VWadsworth]

That's brilliant thank you.