Macro Compile Error: Procedure too large

[Jediantman]

Hi there,


I'm currently updating the first macro I ever wrote, as every quarter it evolves into a slightly bigger operation. The previous two incarnations have worked without a problem but now I get the 'Compile Error: Procedure too large' message appear. Frustrating doesn't begin to describe it. I've had a play around and removed a lot of the unnecessary code, such as ActiveWindow.Scroll, but to no avail. The main problem, I'm fully aware, is the repetitive nature of the task I use it for.


I have 15 individual workbooks, all with identical worksheets, that are updated by my team as they complete their work. What my macro does is open each one, copy a range of cells, switch to my 'Master', then paste to the relevant spot I want. The issue gets more complicated by the fact that each of the 15 workbooks has up to 17 worksheets so my macro is just constant repetition of the same tasks, just with different filenames. I am thinking at the moment of just splitting it into two parts to make it easier but as a macro novice I don't know if there is a way to cut down the amount of code to begin with or maybe link 15 separate once's together?


An example of my code is below, it just keeps going on, and on, and on, and on...

[pre]

'Open Aidan's Tracker, select S5 cells and copy

Workbooks.Open Filename:= _
"FilepathSum2013 Tracker Aidan.xlsx" _
, ReadOnly:=True, Notify:=False
Sheets("Section 5").Select
Range("A23:CH163").Select
Selection.Copy
Windows("SUM13_Datamastermacro_v2.0.xlsm").Activate
Sheets("Section5Master").Select
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A164").Select

'Go back to Aidan's Tracker and select S5 - SM tab, select cells and copy

Windows("Sum2013 Tracker Aidan.xlsx").Activate
Sheets("S5 Cat. - SM").Select
Range("A23:CJ43").Select
Selection.Copy
Windows("SUM13_Datamastermacro_v2.0.xlsm").Activate
Sheets("S5 Cat - SM Master").Select
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A44").Select

After I've copied each of my worksheets I then have the code to close each workbook:

'Close Aidan's Tracker

Windows("Sum2013 Tracker Aidan.xlsx").Activate
Application.DisplayAlerts = False
ActiveWindow.Close

[/pre]
As always, any advice and help is greatly appreciated.

 

[Luke M]

Do all the worksheets get copied?

How do you know which cells to copy?

Do all the destination worksheets in your master workbook follow a pattern?

Do all the data get pasted into cell A23 of their respective sheets?

 

[Jediantman]

All the worksheets get copied from each of the individual workbooks. There are differing amounts of data, hence the differing ranges specified, however they all follow an identical . In the example above Aidan's file is simply the first one so his data gets pasted into A23, the next person's will go into A164 (on the Section5Master sheet), then A305 for the next person, so there is a 140 row gap for each person to accommodate all I need, with a little empty space to ensure no data is lost when the next lot is copied onto the sheet. The other sheets don't have as much data so I copy between 10 and 50 rows from each, just so that when I want to remove the gaps there is less to be deleted.

 

[NARAYANK991]

Hi Ant ,


If you go through this link , it specifically talks of this happening when there is too much of repetitive code ; what you should be doing is using a subroutine to perform the repetitive task , and pass the relevant data to the subroutine as parameters.


http://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel


If you cannot use parameters , the next best would be to use global variables , and set these before calling the subroutine.


Narayan