how do i break a large procedure

[excel77]

I have produce this spreadsheet, which is attached. it is for training needs in my company. if a name is clicked in column A it shows against a course scheduled for the individual and also transfer this info to a print page , which i have called Individual, and Individual 2.

now i want to run the whole program but its giving me this compile error: Procedure too large. How can i break this procedue.

I need help

[dominicb]

Good evening excel77

now i want to run the whole program but its giving me this compile error: Procedure too large. How can i break this procedue.
I need help

Your procedure is too large (that bit you already know) - the general advice for this is to break your code up into smaller modules and daisy chain them together.

It's not an ideal answer, I know, but VBA's nor perfect and you've hit one of its limitations. the msdn page dealing with such a limitation is here - at least it tells you how big your app can be, so you can see where you've gone wrong for next time.

HTH

DominicB

[Richard Buttrey]

Wow, that's some procedure...I never knew that error message existed

Seriously though you need to break that down. A quick glance shows that the With Worksheets(1)....End With instruction blocks are essentially the same, with only the Range address changing.

Whenever you see constantly repeating instructions like that, it means it's crying out to have them eliminated and replaced with a loop inside which you have just one block of instructions. The paste destination ranges, (with a few odd exceptions which need to be examined) vary by one row and this could obviously be calculated from the value of the loop variable.

I haven't looked at whether your approach is the most efficient. Can you expand on your brief description about what you want to achieve? There may be better ways, maybe not even involving macros.

Rgds

[Leith Ross]

Hello Excel77,

We could answer your question faster if you would explain what you want the code to do. It looks like a simple search routine. A good explanation would help in rewriting the code in a faster and more efficient manner.

Sinccerely,
Leith Ross

[Bob Phillips]

I think your code is wrong too! You start in increments of 6 rows, then suddenly you increment by 7 at one point. And you skip rows in the target area.