xl could be recording in steps, because you have to do each thing one step at a
time.

But once you get more familiar with code, you'll find that you record macros to
get syntax/keywords/arguments, but then change them to something that is easier
to understand (and takes less time to run).

But that comes through experience.

And almost all objects are children to something.

Ranges are in a worksheet.
Worksheets are in a workbook.
Workbooks are in the application.
And the application is in...

Sometimes, it's nice to refer to the worksheet with its own variable. Sometimes
(if you're not using it very much), it's just as easy to use .parent (of a
range).

=====
ps. One of the way to learn is to frequent this newsgroup. You'll see lots of
styles -- some you like and some you don't.

But after you know more, you'll realize how difficult it is to modify any macro
that was developed by recording a macro while doing it manually. So much
depends on the selection (and the layout of the data in the worksheet), that it
can be a miserable job to make it pretty.

RLN wrote:

Dave,
This example worked very nicely...thank you!

I do have a couple of questions on a few lines of code you provided,
not because I doubt your expertise here, but only because I want to
understand a little more what Excel is doing.

Set DestCell = Worksheets.Add(after:=Sheets(Sheets.Count)).Range( "A1")<<
As I stepped through the code with the debugger, your line added the
new blank sheet to the workbook and worked fine.
However, when I recorded a macro to do the same thing, Excel gave me
these three lines of code:
-Sheets.Add
-Sheets("Sheet11").Select
-Sheets("Sheet11").Name = "NEWSHEET10" 'renamed the sheet

I'm just wondering why Excel wouldn't provide better code where you
can set properties ("after:=") on the same line. I haven't written
too many macros inside of Excel and it would have been good for Excel
to provide a line such as what you gave here in your example. (It's
Microsoft...I know, and that is why we have these newsgroups.)

I did not know that this:
-Sheets("Sheet11").Name = "NEWSHEET10" 'renamed the sheet
is the same as this....
-DestCell.Parent.Name = "Split_" & Format(pCtr, "000")
...only in your example I like how you used the format function here.
I was not aware of the "parent.Name" property.

Finally....
.Rows(lCtr).Resize(myStep).Copy Destination:=DestCell
I didn't realize this would do a copy/paste in the same line of code.
All of my macros I tried to record did them in two steps and they did
not work very well when performing it in a bulk dump fashion to
multiple sheets.

Again Dave, thank you for your example, it works very nicely, and I
appreciate your assistance here.

--

Dave Peterson