automatically select next group of cells

[iturnrocks]

I have a workbook that contains part numbers and their components listed like below. There is always one empty row between the part number and the components, and then 3 empty rows between the last component and the next part number. The number of components varies from sometimes only 1 up to 25. Currently, I select a part number and its components like B1:C5 and then I have a macro that copies it to another workbook. What I would like to do is somehow get the macro to automatically select the next set down, in this case, B9:C14, so I could copy every set over to its proper place with only one click of the button.

If anyone knows how to do this, I appreciate the help.

Please Login or Register  to view this content.

[mikerickson]

If you have one data group selected. This will select the next data group.

Please Login or Register  to view this content.

[iturnrocks]

If you have one data group selected. This will select the next data group.

I tried it and it selected the next set along with every set after that.

My example only showed 2 sets, but there are hundreds.

I need it to only select the next set, then my macro will run again, then select the next set and so on.

[mikerickson]

The overall plan should be

Please Login or Register  to view this content.

If you post your code, it would be easier to integrate the "next group" part. I'm going to be leaving town for the weekend. I'll look at this again when I get back to see if you need any more help.

[iturnrocks]

Thanks. I had no luck on my workbook, so I opened a new blank one, just copied in my example from the post and it worked. I still have to figure out why it wont work on my sheet. If I cant figure it out, perhaps I will post my workbook later. Im just kinda embarrassed to show off my amateur excel skills.

[mikerickson]

Are you putting your code in a sheet's code module or in a normal module?

[iturnrocks]

I dont know much about different kinds of modules. Its attached to an active x command button in the sheet code.

[iturnrocks]

Here are my files. Ive included both 97-03 excel files and 2007 files since I use 2007 and im not sure if you do. Functionality of the select text function is not affected by the different files, but my Data file goes beyond the 255 column limit of 97-03 files. The zip files are each 300-400k which is larger than the forum will host, so Ive created a directory on my website for them.

Selectnext.zip is the 97-03 file
Selectnext07.zip is the 07 file

http://www.ksvp.org/excel/

The zip files include 3 files. autobuildcopyover file hosts the SelectNext function on Sheet 2.

The Data1 file is included in case you want to know what my other code does.

The nfo.txt file just explains the multiple files like Ive explained here. I just now decided I should mention the multiple files before you opened the zip.


Edit: Files since removed

[shg]

Try this:

Please Login or Register  to view this content.

[iturnrocks]

Try this:

That worked. Thank you very much.

Its late and I will take a better look tomorrow, but it seems to work perfectly.

Thanks again for everyones help.

[shg]

My code was only a minor tweak to Mike's. You're welcome.