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. :-)

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. :-)

Thanks Jim...

I know that the logic will get screwed up if I start deleting things from
the top down...but does it matter if I code something as

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

as opposed to

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

???? At this point of the code I was just selecting the rows, but I had
previously had the exact same line of code operate on another sheet and
subsequently do the deletion. So it could be a "confusion" issue...

And yes, I do know that concantenation is "generally" done with an
ampersand. Actually I've used languages where the option to use a plus sign
did not exist. Just a bad habit when doign VB/VBA coding. Heck, I've only
recently become anal regarding indenting, mainly because .NET forces it on a
developer, so I've kind of bent in the wind.

Again, I appreciate the input.

Ed,

What I was referring to was not changing the range description...
Rows("2:4").Delete works the same as Rows("4:2").Delete

But the fact that if you delete rows 2:4 then row 6 becomes row 3.
That is very difficult for any program to track.

Delete the highest numbered row(s) first... row 6 then rows 2:4

Regards,
Jim Cone
San Francisco, CA

"Ed Ardzinski" wrote in message ...
Thanks Jim...

I know that the logic will get screwed up if I start deleting things from
the top down...but does it matter if I code something as

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

as opposed to

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

???? At this point of the code I was just selecting the rows, but I had
previously had the exact same line of code operate on another sheet and
subsequently do the deletion. So it could be a "confusion" issue...

And yes, I do know that concantenation is "generally" done with an
ampersand. Actually I've used languages where the option to use a plus sign
did not exist. Just a bad habit when doign VB/VBA coding. Heck, I've only
recently become anal regarding indenting, mainly because .NET forces it on a
developer, so I've kind of bent in the wind.

Again, I appreciate the input.