Ed,

Excel has difficulty keeping track of rows when you delete them
going from the top down. It works much better going from the bottom up.
Also, the conventional method to join strings is to use "&" not "+".

Regards,
Jim Cone
San Francisco, CA

"Ed Ardzinski" wrote in message ...
This one files under "it's fixed, so what?", but I'm still curious.

I've written a macro that copies a few sheets into a workbook, then
proceeds to create copies of the existing report sheets, changes a few fields
so the copied sheets will display their proper subset of data.

All was going fine, until I started the last step-culling rows from the
copied sheets that were not needed. I have a data sheet that allows me to
make these determinations...the logic all seemed to come together well, and
aside from the usual stupid bugs (adding 1 to the wrong expression or
forgetting to increment a loop counter) I was pretty happy.

Then I got an error 1004, on a line of code that just prior to being
executed worked fine for another one of the copied sheets. basically the
line of code is:

Rows(CStr(nPlans + 1 + 27) + ":54").Select

As I said, a few lines earlier in the Macro this expression resolves
correctly, the Rows are selected (and subsequently deleted). I suspected
that maybe there was something wrong in the particular sheet, so I ran a test
copying the sheet that worked, deleting the one that didn't, and renaming the
new sheet. No go.

Eventually, through playing in the immediate window while debugging I came
across putting Range("A1").Select before trying to select the rows, and BINGO
things worked fine.

The only "reason" I can think of is that thise line of code is an exact
duplicate of one that fired off previously. The proper sheet is selected
prior to trying to execute the code, but maybe the selection of a cell in the
new sheet is required so that VBA can "get its bearings"...admittedly I'm
stretching.

As I said, it works, the extra line of code does not cause any execution
issues, so I'm inclined to just say "move on. nothing to see here". But I'm
just wondering why Sheets(str + " D").Select (str is a string variable passed
to the sub representing the prefix of the sheet name) is not enough to let
VBA know that my Rows selection applied to that sheet, not the last one that
executed the same line of code...

Thanks for any attempts to broach this. :-)