VBA - transfer specific data within a sheet to specific sheet by unique identifier

[JDJong]

Hey Mr. Excels,

I am currently running a project that requires an automation on data.

I have altogether 78 sheets:

- 3 sheets are output data.
- these 3 sheets are differentiated by payment types.
- the rest of the 75 sheets are category sheets
- category is located in J column in every sheet.

[TABLE="width: 1713"]
<colgroup><col span="12"><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]ZIP/Postal Code[/TD]
[TD]Country/Region[/TD]
[TD]Salesperson[/TD]
[TD]Region[/TD]
[TD]Shipped Date[/TD]
[TD]Shipper Name[/TD]
[TD]Ship Name[/TD]
[TD]Ship Address[/TD]
[TD]Ship City[/TD]
[TD]Ship State[/TD]
[TD]Ship ZIP/Postal Code[/TD]
[TD]Ship Country/Region[/TD]
[TD]Payment Type[/TD]
[TD]Product Name[/TD]
[TD]Category[/TD]
[TD]Unit Price[/TD]
[TD]Quantity[/TD]
[TD]Revenue[/TD]
[TD]Shipping Fee[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/29/14[/TD]
[TD]Shipping Company B[/TD]
[TD]Karen Toh[/TD]
[TD]789 27th Street[/TD]
[TD]Las Vegas[/TD]
[TD]NV[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Check[/TD]
[TD]Beer[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$14,00[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]$266,00[/TD]
[TD="align: right"]$25,80[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/29/14[/TD]
[TD]Shipping Company B[/TD]
[TD]Karen Toh[/TD]
[TD]789 27th Street[/TD]
[TD]Las Vegas[/TD]
[TD]NV[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Check[/TD]
[TD]Dried Plums[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$3,50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]$210,00[/TD]
[TD="align: right"]$20,16[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZIP/Postal Code[/TD]
[TD]Country/Region[/TD]
[TD]Salesperson[/TD]
[TD]Region[/TD]
[TD]Shipped Date[/TD]
[TD]Shipper Name[/TD]
[TD]Ship Name[/TD]
[TD]Ship Address[/TD]
[TD]Ship City[/TD]
[TD]Ship State[/TD]
[TD]Ship ZIP/Postal Code[/TD]
[TD]Ship Country/Region[/TD]
[TD]Payment Type[/TD]
[TD]Product Name[/TD]
[TD]Category[/TD]
[TD]Unit Price[/TD]
[TD]Quantity[/TD]
[TD]Revenue[/TD]
[TD]Shipping Fee[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Andrew Cencini[/TD]
[TD]East[/TD]
[TD="align: right"]12/06/14[/TD]
[TD]Shipping Company A[/TD]
[TD]Christina Lee[/TD]
[TD]123 4th Street[/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Credit Card[/TD]
[TD]Dried Pears[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$30,00[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]$2.430,00[/TD]
[TD="align: right"]$255,15[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Andrew Cencini[/TD]
[TD]East[/TD]
[TD="align: right"]12/06/14[/TD]
[TD]Shipping Company A[/TD]
[TD]Christina Lee[/TD]
[TD]123 4th Street[/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Credit Card[/TD]
[TD]Dried Apples[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]$53,00[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]$4.399,00[/TD]
[TD="align: right"]$461,90[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Andrew Cencini[/TD]
[TD]East[/TD]
[TD="align: right"]12/06/14[/TD]
[TD]Shipping Company A[/TD]
[TD]Christina Lee[/TD]
[TD]123 4th Street[/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Credit Card[/TD]
[TD]Dried Plums[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$3,50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]$262,50[/TD]
[TD="align: right"]$26,25[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/14/14[/TD]
[TD]Shipping Company B[/TD]
[TD]John Edwards[/TD]
[TD]123 12th Street[/TD]
[TD]Las Vegas[/TD]
[TD]NV[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Credit Card[/TD]
[TD]Chai[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$18,00[/TD]
[TD="align: right"]97[/TD]
[TD="align: right"]$1.746,00[/TD]
[TD="align: right"]$183,33[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/14/14[/TD]
[TD]Shipping Company B[/TD]
[TD]John Edwards[/TD]
[TD]123 12th Street[/TD]
[TD]Las Vegas[/TD]
[TD]NV[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Credit Card[/TD]
[TD]Coffee[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]$46,00[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]$2.806,00[/TD]
[TD="align: right"]$291,82[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Nancy Freehafer[/TD]
[TD]North[/TD]
[TD="align: right"]12/10/14[/TD]
[TD]Shipping Company C[/TD]
[TD]Elizabeth Andersen[/TD]
[TD]123 8th Street[/TD]
[TD]Portland[/TD]
[TD]OR[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Credit Card[/TD]
[TD]Chocolate Biscuits Mix[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]$9,20[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]$257,60[/TD]
[TD="align: right"]$24,47[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ZIP/Postal Code[/TD]
[TD]Country/Region[/TD]
[TD]Salesperson[/TD]
[TD]Region[/TD]
[TD]Shipped Date[/TD]
[TD]Shipper Name[/TD]
[TD]Ship Name[/TD]
[TD]Ship Address[/TD]
[TD]Ship City[/TD]
[TD]Ship State[/TD]
[TD]Ship ZIP/Postal Code[/TD]
[TD]Ship Country/Region[/TD]
[TD]Payment Type[/TD]
[TD]Product Name[/TD]
[TD]Category[/TD]
[TD]Unit Price[/TD]
[TD]Quantity[/TD]
[TD]Revenue[/TD]
[TD]Shipping Fee[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/14/14[/TD]
[TD]Shipping Company B[/TD]
[TD]John Edwards[/TD]
[TD]123 12th Street[/TD]
[TD]Las Vegas[/TD]
[TD]NV[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$0,00[/TD]
[TD="align: right"]$8,00[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Nancy Freehafer[/TD]
[TD]North[/TD]
[TD="align: right"]12/10/14[/TD]
[TD]Shipping Company C[/TD]
[TD]Elizabeth Andersen[/TD]
[TD]123 8th Street[/TD]
[TD]Portland[/TD]
[TD]OR[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Cash[/TD]
[TD]Mozzarella[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]$34,80[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]$2.923,20[/TD]
[TD="align: right"]$300,85[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Andrew Cencini[/TD]
[TD]East[/TD]
[TD="align: right"]12/06/14[/TD]
[TD]Shipping Company C[/TD]
[TD]Christina Lee[/TD]
[TD]123 4th Street[/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$0,00[/TD]
[TD="align: right"]$9,00[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Jan Kotas[/TD]
[TD]West[/TD]
[TD="align: right"]12/31/14[/TD]
[TD]Shipping Company B[/TD]
[TD]Soo Jung Lee[/TD]
[TD]789 29th Street[/TD]
[TD]Denver[/TD]
[TD]CO[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$0,00[/TD]
[TD="align: right"]$23,00[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/05/14[/TD]
[TD]Shipping Company B[/TD]
[TD]Thomas Axerr[/TD]
[TD]123 3rd Street[/TD]
[TD]Los Angelas[/TD]
[TD]CA[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Cash[/TD]
[TD]Syrup[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]$10,00[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]$280,00[/TD]
[TD="align: right"]$90,25[/TD]
[/TR]
[TR]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Mariya Sergienko[/TD]
[TD]West[/TD]
[TD="align: right"]12/05/14[/TD]
[TD]Shipping Company B[/TD]
[TD]Thomas Axerr[/TD]
[TD]123 3rd Street[/TD]
[TD]Los Angelas[/TD]
[TD]CA[/TD]
[TD="align: right"]99999[/TD]
[TD]USA[/TD]
[TD]Cash[/TD]
[TD]Curry Sauce[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]$40,00[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]$480,00[/TD]
[TD="align: right"]$239,12[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1626"]
<colgroup><col span="11"><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 18"]I would like to have them transfer to the respective sheet by category. (All type 1 goes to overview category type 1 sheet); and track the changes whenever a new entry is recorded, all the sheets ( category type and the payment type sheet will be automatically update)

Any inputs greatly appreciated.

rgds,
J.[/TD]
[/TR]
</tbody>[/TABLE]

 

[JDJong]

anyone could help?

 

[rpaulson]

if I understand:

you need a macro that will loop through 75 sheets and look in column "J" for a category.
then write the entire row to a sheet that is name that category.
so if there are 500 unique categories then 500 new sheets will be added?

if the above is correct. then I would suggest you combine all you sheet into one master sheet then create Pivot tables, and charts as needed. it woule be much cleaner and more efficient.

hth,

Ross