Compile Error: Procedure too large

[jontherev]

I have a large macro that copies from an old workbook, into a new one (truncated version below) and I would like some help splitting it up. I'm terrible at vba, so please excuse my poor attempt (also below). Any help is very appreciated. I have no idea what I'm doing, so I'm sure it's a dumb newbie mistake. Thanks.

Macro sample code:

Please Login or Register  to view this content.

My attempt at splitting up my large macro:

Please Login or Register  to view this content.

[Norie]

Instead of splitting up the code you should probably look at paring it back a bit.

For example, you don't need any of these as you are, going on the posted code anyway, fully qualifying everything properly.

Please Login or Register  to view this content.

If you were to remove each instance of this type of code you would cut the code down a fair bit, even in the small sample posted you'd lose 81 (out of 331) lines of code.

Also, you don't need to repeat this over and over.

Please Login or Register  to view this content.

If you make those changes and eliminate some blank lines you could cut the length of the posted code by more than a half.

Please Login or Register  to view this content.

[jontherev]

THANKS! I'm uncertain if it will still fail once I'm done paring it down, but this will still help tremendously. What you saw is about 20% of what I've got to import (which will likely grow slightly over time). If it fails, I will post again; otherwise, thanks again for the rescue.

[romperstomper]

You could also use an array:

Please Login or Register  to view this content.

If you can put the array data (i/e the range addresses/names to copy) into a range of cells, the code would be even neater.

[jontherev]

Thanks Romperstomper, I will definitely try to incorporate that as well. One last question:

Is there some code to tell it NOT to perform a copy IF a named cell doesn't exist in the old workbook? This causes an error. I only need to add this on newly-added cells that are named in the future. Example: the following code fails, because WildShapeForm1 doesn't exist in wb2...and would this code look different if I incorporated into the array suggestion above? You guys are awesome, thanks so much!

Please Login or Register  to view this content.

[romperstomper]

For example, using my code:

Please Login or Register  to view this content.

[jontherev]

I am having trouble with the array, but the other method is working pretty well. Is there a way to not copy cells (whether named cells or individual cells within a specified range) that are blank? And would this work if you are trying to copy a cell that doesn't exist (i.e. a named cell in the updated workbook that doesn't exist in the old workbook you are importing from)? I've added , SkipBlanks:=True, in hopes that that would cut down significantly on the run time, but it doesn't seem to reduce the run time of the macro at all (~15 minutes). Any ideas on how to stop it from even selecting, or copying the blank cells (or named cells that don't exist)? Most of my importing cells are blank and don't need to be imported at all, so this would help tremendously. Note, that some ranges may have only 1 non-blank cell within the range and some ranges are only a single (usually named) cell. Here is a much truncated version of this macro:

Please Login or Register  to view this content.

When I try the same thing for the array method, it never prompts me to even open the target file and gives the following Compile error: Variable not defined, highlighting the first time the line "Set rgFrom = " appears. I would like it to also skip blanks when copying if possible, in order to hopefully reduce the run time. I'm also not sure if my syntax is correct in how I added additional worksheets...again, I'm vba-ignorant. Here's my failed attempt:

Please Login or Register  to view this content.

Thanks again, both of you. You've been such great help already and I really appreciate it.

[jontherev]

Forgot to mention that I can't upload my workbook due to copyright issues (don't want to be sued by Wizards of the Coast). Thanks again to anyone who might know the answers to my last post. I guess technically this is solved and I should post a new thread about skipping blanks...