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

JDJong

New Member
Joined
May 2, 2018
Messages
39
Hey Mr. Excels,:cool:

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.

ZIP/Postal CodeCountry/RegionSalespersonRegionShipped DateShipper NameShip NameShip AddressShip CityShip StateShip ZIP/Postal CodeShip Country/RegionPayment TypeProduct NameCategoryUnit PriceQuantityRevenueShipping Fee
99999USAMariya SergienkoWest12/29/14Shipping Company BKaren Toh789 27th StreetLas VegasNV99999USACheckBeer1$14,0019$266,00$25,80
99999USAMariya SergienkoWest12/29/14Shipping Company BKaren Toh789 27th StreetLas VegasNV99999USACheckDried Plums2$3,5060$210,00$20,16
ZIP/Postal CodeCountry/RegionSalespersonRegionShipped DateShipper NameShip NameShip AddressShip CityShip StateShip ZIP/Postal CodeShip Country/RegionPayment TypeProduct NameCategoryUnit PriceQuantityRevenueShipping Fee
99999USAAndrew CenciniEast12/06/14Shipping Company AChristina Lee123 4th StreetNew YorkNY99999USACredit CardDried Pears3$30,0081$2.430,00$255,15
99999USAAndrew CenciniEast12/06/14Shipping Company AChristina Lee123 4th StreetNew YorkNY99999USACredit CardDried Apples4$53,0083$4.399,00$461,90
99999USAAndrew CenciniEast12/06/14Shipping Company AChristina Lee123 4th StreetNew YorkNY99999USACredit CardDried Plums5$3,5075$262,50$26,25
99999USAMariya SergienkoWest12/14/14Shipping Company BJohn Edwards123 12th StreetLas VegasNV99999USACredit CardChai6$18,0097$1.746,00$183,33
99999USAMariya SergienkoWest12/14/14Shipping Company BJohn Edwards123 12th StreetLas VegasNV99999USACredit CardCoffee6$46,0061$2.806,00$291,82
99999USANancy FreehaferNorth12/10/14Shipping Company CElizabeth Andersen123 8th StreetPortlandOR99999USACredit CardChocolate Biscuits Mix7$9,2028$257,60$24,47
ZIP/Postal CodeCountry/RegionSalespersonRegionShipped DateShipper NameShip NameShip AddressShip CityShip StateShip ZIP/Postal CodeShip Country/RegionPayment TypeProduct NameCategoryUnit PriceQuantityRevenueShipping Fee
99999USAMariya SergienkoWest12/14/14Shipping Company BJohn Edwards123 12th StreetLas VegasNV99999USACash8$0,00$8,00
99999USANancy FreehaferNorth12/10/14Shipping Company CElizabeth Andersen123 8th StreetPortlandOR99999USACashMozzarella9$34,8029$2.923,20$300,85
99999USAAndrew CenciniEast12/06/14Shipping Company CChristina Lee123 4th StreetNew YorkNY99999USACash12$0,00$9,00
99999USAJan KotasWest12/31/14Shipping Company BSoo Jung Lee789 29th StreetDenverCO99999USACash11$0,00$23,00
99999USAMariya SergienkoWest12/05/14Shipping Company BThomas Axerr123 3rd StreetLos AngelasCA99999USACashSyrup11$10,0049$280,00$90,25
99999USAMariya SergienkoWest12/05/14Shipping Company BThomas Axerr123 3rd StreetLos AngelasCA99999USACashCurry Sauce15$40,0029$480,00$239,12

<colgroup><col span="12"><col><col><col><col span="4"></colgroup><tbody>
</tbody>

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.

<colgroup><col span="11"><col><col><col><col span="4"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,122
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,446
Messages
5,547,971
Members
410,820
Latest member
Prepost
Top