Copy and pasting (through vba) specific rows to new sheet

[JPSIMMON]

I am looking to go through a large chart (A19:CC219) and paste rows with a specific criteria from one of the columns to another sheet. Anyone have a code that can do this?
The goal is to auto export info added to the 'master' sheet to sub sheets with that certain column of criteria.

Thanks for the help!

[Parth007]

can you provide the sample input sheet with expected output sheet?

[vcoolio]

Good day JP & Parth,

If I have understood JP correctly, I believe that he/she would like the vast dataset (in the "Master" sheet) divided up into different work sheets based on a criteria in say Column A with the criteria identifying with the sheet tab (name). Following are two options I have come up with to do this:-

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The first option sends all data to its relevant sheet on execution of the code. The second option uses an Input Box asking for the required criteria to typed into it and then sends the data to the relevant sheet from there. This allows the User to select which rows of data to be transferred at random (and when the User chooses to).

Attached is my test work book for you to peruse. The data starts in A2 so the ranges should be changed to suit. The criteria are in Column A (just random letters). Please excuse the gawdy colours but I thought it would be a good way of identifying what's going on. Ignore the code in Module 2 (I was too lazy to colour all the rows manually!)

JP, just like Parth, I'm still interested in seeing a sample of your work book.

Cheerio people.
vcoolio.

[JPSIMMON]

coolio, thank you for those. Going through your file they both do exactly as I need. Assuming my criteria that I want dictating the copy/paste starts in cell J19 on 'Master' how can I alter this formula? I can't seem to figure it out.

[JPSIMMON]

And also mentionable: my Headings row is 9, and all of the headings already have filters on them.

[vcoolio]

Hello JP,

Change this line of code:-

Please Login or Register  to view this content.

to this:-

Please Login or Register  to view this content.

and it should all fall into place for you.

Cheerio,
vcoolio.

[JPSIMMON]

Thanks Coolio, worked awesome. Every time I run it will it replace or just stack the data onto the destination sheet? Is there anyway it cannot stack, and just replace?
As well as after I ran the code it erased my Filters on the 'Master', the headings with Filters are in Row 9 - any way I can have them left?

Thanks again,
simm

[vcoolio]

Hello JP,

Every time I run it will it replace or just stack the data onto the destination sheet? Is there anyway it cannot stack, and just replace?

To prevent stacking, add the following line of code:-

Please Login or Register  to view this content.

in between these two lines of code:-

Please Login or Register  to view this content.

As well as after I ran the code it erased my Filters on the 'Master', the headings with Filters are in Row 9 - any way I can have them left?

Put an apostrophe (') in front of the two lines of code:-

Please Login or Register  to view this content.

This will just comment out the code lines so they become inactive. See if that does the job for you.

You could also try the following code which won't erase the filters:-

Please Login or Register  to view this content.

Of course, change your range to suit:-

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

[JPSIMMON]

Coolio, you are the man. Thanks. It seems to be working. In the mood to adding a line?

I am also looking to add a line to make column widths in the past pages the same as the 'Master'-- should I start new thread?

Thanks again,

[vcoolio]

Hello JP,

In the macros named FindValues() and TransferData2(), add the following line of code:-

Please Login or Register  to view this content.

just above;-

Please Login or Register  to view this content.

(At the bottom of the codes).

For the macro TransferAllData, add the following in the same position:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

[JPSIMMON]

Just about done coolio, thanks for that.

If I am using TransferData2()
Which line would I insert the range if I want it pasting to (J19:J) in the destination sheet?

Please Login or Register  to view this content.

[vcoolio]

Hello JP,

You want the pasting to start at J19 and not at A19 in all the destination sheets?

Does this mean that you have headings/titles etc. in row 18 of each destination sheet?

Cheerio,
vcoolio.

[JPSIMMON]

Exactly correct

[vcoolio]

Hello JP,

I've amended the code to cover all angles for you (I think!):-

Please Login or Register  to view this content.

Good luck with it all!

Cheerio,
vcoolio.

[JPSIMMON]

I apologize, I want to start pasting the rows from A19:A. My data starts in row 19, sorry my mistake.
How do I do that?

[vcoolio]

Hello JP,

Just a couple of subtle changes to the code should do it:-

Please Login or Register  to view this content.

You'll see them if you read through it line by line. I'm assuming that the criteria is still in Column J.

I hope that this helps.

Cheerio,
vcoolio.

[Parth007]

MAy be...

Please Login or Register  to view this content.

slight change in vcoolio's code :D

[JPSIMMON]

Hey guys any reason why the data is posting in the destination sheet in row 15 versus the desired row row 19?

[vcoolio]

Hello JP,

All is working as it should in my test work book.

The only reasons I can think of is that you have a "typo" somewhere in the cell references or that you have changed your Headings/Titles etc. to row 14.

Check and see if these are the causes.

Cheerio,
vcoolio.

[Doc.AElstein]

Hi JPSIMMON,
. I have been following this thread and learning from it an alternative to a method that I have often used to solve such a problem.
. By not supplying the info initially continually asked for, you have made a lot of unnecessary work for those trying to help you.
. Even at this stage, it would be helpful if you could upload a sample file with reduced rows, and desensitised data in the Master sheet along with another sheet which you hand fill in yourself which looks exactly as it should after the running of a code based on the actual sample data in the Master Sheet. At the very least you could do that with the sample File that vcoolio has kindly given you.
.
.. Anyway for what it is worth, here would be my alternative method version following vcoolio idea of a Message box to ask for the search criteria to look for in column J

Please Login or Register  to view this content.

. In the next Post I attempt to show you what all our codes are currently doing, based on the example file that vcooly did for you.

[Doc.AElstein]

. All the codes given to you in this Thread are doing something similar to this. ( The exact example is based on my code Sub TransferDataCoolioAlan() given in Post #20 and using similar test Data to that vCooly did for you: )


Here an extract from your ( vCooly’s ) Master sheet before ( and after ) running the macro


Using Excel 2007

-	A	B	C	D	E	F	G	H	I	J	K
17
18	Heading	Heading	Heading	Heading	Heading	Heading	Heading	Heading	Heading	Criteria	Heading
19	Data11	Data21	Data31	Data41	Data51	Data61	Data71	Data81	Data91	DD	Data111
20	Data12	Data22	Data32	Data42	Data52	Data62	Data72	Data82	Data92	EE	Data112
21	Data13	Data23	Data33	Data43	Data53	Data63	Data73	Data83	Data93	FF	Data113
22	Data14	Data24	Data34	Data44	Data54	Data64	Data74	Data84	Data94	GG	Data114
23	Data15	Data25	Data35	Data45	Data55	Data65	Data75	Data85	Data95	AA	Data115
24	Data16	Data26	Data36	Data46	Data56	Data66	Data76	Data86	Data96	BB	Data116
25	Data17	Data27	Data37	Data47	Data57	Data67	Data77	Data87	Data97	CC	Data117
26	Data18	Data28	Data38	Data48	Data58	Data68	Data78	Data88	Data98	DD	Data118
27	Data19	Data29	Data39	Data49	Data59	Data69	Data79	Data89	Data99	EE	Data119

Master

After running the code and selecting, for Example DD, as the IDSelection here is an extract of what you get this in sheet DD

Using Excel 2007

-	A	B	C	D	E	F	G	H	I	J	K
17
18	Heading	Heading	Heading	Heading	Heading	Heading	Heading	Heading	Heading	Criteria	Heading
19	Data11	Data21	Data31	Data41	Data51	Data61	Data71	Data81	Data91	DD	Data111
20	Data18	Data28	Data38	Data48	Data58	Data68	Data78	Data88	Data98	DD	Data118

DD

Note: My code is very fast, but does not preserve the color Formating, only copying data. Those codes from Parth and vCooly do preserve the Format but take a little longer. Take your Pick!

Alan

[JPSIMMON]

Appreciate that Alan, I will experiment with both.

[Doc.AElstein]

Appreciate that Alan, I will experiment with both.

You are welcome,
Please let us all know how you get on
Alan
P.s. Please consider supplying the info we asked for if you need more help, that is to say a file. That would hve / would make it a lot easier to help you

[hacke.r]

can you help me? thank you

[Doc.AElstein]

can you help me? thank you

?????? with what?
. Sounds like you need help in knowing how to ask for help????

[hacke.r]

Hi Doc.AElstein

http://www.excelforum.com/excel-prog...ml#post3943853

[protonLeah]

hacke.r,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.