How do I Select All Rows With a Matching Value in VBA

metamer6

New Member
Joined
Sep 19, 2006
Messages
1
Hello,

I'm just learning VBA and having one hell of a time with this problem in VBA. Any help you can give is much appreciated!

I need to copy the entire row for every cell in column A with a certain value then paste to a new sheet. The trick is that the number of values involved is a variable as well as the number of rows that have that value.

For example, say I have a sheet that shows journal entries for a given year. January might only have 1 entry, February might have 6 entries, no entries for March - April, 10 entries in June, etc. And of course this changes from year to year.

So I need a macro to copy the one entry from January to a new sheet called "January", all six entries from February to a new sheet called "February", March - April wouldn't need a sheet since there's no data, all 10 entries on a new sheet called "June" and so forth.

It's probably also important to note that I only used months as an example to explain the issue. It would actually be a 8-digit number of which there are millions of variations.

I don't have enough experience to know where to start. Any direction or examples you can offer would be a great help. Thanks in advance!

Dave
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
Hello,

I'm just learning VBA and having one hell of a time with this problem in VBA. Any help you can give is much appreciated!

I need to copy the entire row for every cell in column A with a certain value then paste to a new sheet. The trick is that the number of values involved is a variable as well as the number of rows that have that value.

For example, say I have a sheet that shows journal entries for a given year. January might only have 1 entry, February might have 6 entries, no entries for March - April, 10 entries in June, etc. And of course this changes from year to year.

So I need a macro to copy the one entry from January to a new sheet called "January", all six entries from February to a new sheet called "February", March - April wouldn't need a sheet since there's no data, all 10 entries on a new sheet called "June" and so forth.

It's probably also important to note that I only used months as an example to explain the issue. It would actually be a 8-digit number of which there are millions of variations.

I don't have enough experience to know where to start. Any direction or examples you can offer would be a great help. Thanks in advance!

Dave

Dave,

Look at this thread that I responded to, the coding is at the bottom of the thread. Basically I think it is along the same lines as what you are after. In this example the poster wanted to take students who were listed on one sheet and copy their information to sheets that were their teachers.

Keep in mind the problem with millions, since in my example we are speaking of sheets. The next step then is to test for the names of these sheets and if a needed one does not exist then create it. There are several examples of creating this scheme. Most I think revolve around the idea of activating a sheet. If the code errored, then the sheet doesn't exist and you would create it and transfer the data to it. If the code doesn't error, then you know it exist and you transfer the data to it.

http://www.mrexcel.com/board2/viewtopic.php?t=231251&highlight=

The code could be cleaned up a little but seems to work fine.

Perry
 

Forum statistics

Threads
1,141,626
Messages
5,707,486
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top