Procedure Too Big

[wayneshirley]

Hi there,

I am getting an error message "procedure too big." My macro is in Excel 1997-2003 format.

I have commented out as much unnecessary code as I can find.

Is there anything else i can do?

Thank you.

 

[Trevor G]

An array is like grouping sheets together. Sample below

Sub mcrArray()
'A sheet Arrary is like grouping
'You can adjust all the sheets in the group
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Range("A1").Select
End Sub

And

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> PageSetupArraySheets()<br><SPAN style="color:#00007F">Dim</SPAN> wsH <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsH <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>    <SPAN style="color:#00007F">With</SPAN> wsH.PageSetup<br>        .CenterHorizontally = <SPAN style="color:#00007F">False</SPAN><br>        .CenterVertically = <SPAN style="color:#00007F">False</SPAN><br>        .Orientation = xlPortrait<br>        .Zoom = 100<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Next</SPAN> wsH<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Perhaps looping through the sheets would do what you want to create the formula and then fill down. Like this:

Sub loopArray()
Dim lastrow As Long
Dim wsH As Worksheet
For Each wsH In Worksheets
wsH.Activate
Range("M2").FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Range("M2").Select
lastrow = Range("L" & Rows.Count).End(xlUp).Row
Range("M2").AutoFill Destination:=Range("M2:M" & lastrow), Type:=xlFillDefault
Next wsH
End Sub

 

[wayneshirley]

Hi Trevor,

I will try to run this code. Thank you for the guidance on arrays - I have copied this down for future reference.

The totals on each sheet will be on different rows.

Thanks for everything, really appreciate it!

Wayne.