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

J

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>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

Share this page

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
Back
Top