VBA - Macro to Output Data into Tab Based on Criteria

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38
Hi -

Thank you in advance for help on this!

I am trying to create a macro (or maybe multiple?) that looks at a a few fields in a "Raw Data" tab and exports results into a tab called "Opportunities". I have 4 categories listed in a column ("Lifestyle", "Multi", "Tech", "Auto" ). I am trying to create 4 separate buttons in the Opportunities tab for each that follow the following rules:



  • If Sales POD (Column S) = "Lifestyle", "Multi", "Tech", "Auto"
  • If Probability (%) (Column K) >=75
  • Then output the results in the "Opportunities" tab (cant figure out how to post a screen shot but I'd want it to look like the below)
  • Then autosize columns
  • Then format columns C-E to be currency

Data to output into Opportunities

Opportunity NameOpportunity OwnerNewRenewalCarryover
Opportunity 123Bob Smith$109,223$40,774
Opportunity 128Bob Smith$31,060

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>


- Things to keep in mind:



  • I want the "Opportunities" tab to clear / refresh every time to allow for new data to be put in
  • I don't want to output all of the columns in the Raw Data tab in the Opportunities tab. Only the few listed above
  • I want to pull data based on if I pick "Lifestyle", "Multi", "Tech" or "Auto" (why I am thinking multiple buttons?)
  • Column M (R Type) has "New", "Renewal" and "Carryover" relate to "CM Prod" (Column Q)
    • So - in the Opportunities output the $ amount in column Q should be appropriately distributed based on the Opportunity Name (Column C)
    • EX - There could be an Opportunity with the same name (see first two rows) but has $ amount in two different categories (in this example there is $ in Renewal and Carryover)



Opportunity OwnerAccount NameOpportunity NameClient Category2018 Digital Region 2018 RegionVenueCityStateDays at ProbProbability (%)Product NameR TypeS InventoryS ProdExpen SCM PRODDays BucketSales PODS or DAccount POD
Bob SmithAccount 123Opportunity 123FoodUnknownRegion 1: EastVenue 123AtlantaGA55100Sponsorship ProductsRenewalXXX$111,501$45,068$109,22340+MultiSMulti
Bob SmithAccount 123bOpportunity 123TechnologyUnknownRegion 1: EastVenue 124AtlantaGA249100Sponsorship ProductsCarryoverXXX$3,149$2,146$40,77440+EnterpriseSTech
Bob SmithAccount 125Opportunity 125AlcoholUnknownRegion 1: EastVenue 125AtlantaGA144100Sponsorship ProductsRenewalXXX$36,876$92,037$45,76840+LifestyleSLifestyle
Bob SmithAccount 126Opportunity 126AlcoholUnknownRegion 1: EastVenue 126AtlantaGA217100Sponsorship ProductsCarryoverXXX$36,338$44,417$121,22540+LifestyleSLifestyle
Bob SmithAccount 127Opportunity 127Financial ServicesUnknownRegion 1: EastVenue 127AtlantaGA230100Sponsorship ProductsCarryoverXXX$99,261$83,738$21,81540+N/ASMulti
Bob SmithAccount 128Opportunity 128TechnologyUnknownRegion 1: EastVenue 128AtlantaGA61100Sponsorship ProductsNewXXX$112,986$5,710$31,06040+TechSTech
Bob SmithAccount 129Opportunity 129Energy DrinkUnknownRegion 1: EastVenue 129AtlantaGA1100Sponsorship ProductsRenewalXXX$33,692$97,260$52,8410 - 5MultiSMulti
Bob SmithAccount 130Opportunity 130TravelUnknownRegion 1: EastVenue 130AtlantaGA230100Sponsorship ProductsCarryoverXXX$103,634$29,028$58,45040+LifestyleSLifestyle
Bob SmithAccount 131Opportunity 131AlcoholUnknownRegion 1: EastVenue 131AtlantaGA8585Sponsorship ProductsRenewalXXX$114,605$81,118$88,64040+N/ASLifestyle
Bob SmithAccount 132Opportunity 132AlcoholUnknownRegion 1: EastVenue 132AtlantaGA8585Sponsorship ProductsRenewalXXX$93,009$103,432$77,88840+N/ASLifestyle
Bob SmithAccount 133Opportunity 133AlcoholUnknownRegion 1: EastVenue 133AtlantaGA8585Sponsorship ProductsRenewalXXX$101,425$95,077$56,48640+N/ASLifestyle
Bob SmithAccount 134Opportunity 134TelecommunicationsUnknownRegion 2: WestVenue 134New YorkNY330100Sponsorship ProductsCarryoverXXX$124,886$6,294$60,92440+C&TSTech
Bob SmithAccount 135Opportunity 135AlcoholUnknownRegion 2: WestVenue 135New YorkNY144100Sponsorship ProductsRenewalXXX$107,249$120,698$26,57640+LifestyleSLifestyle
Bob SmithAccount 136Opportunity 136Energy DrinkUnknownRegion 2: WestVenue 136New YorkNY230100Sponsorship ProductsCarryoverXXX$43,519$111,415$85540+N/ASLifestyle
Bob SmithAccount 137Opportunity 137Financial ServicesUnknownRegion 2: WestVenue 137New YorkNY230100Sponsorship ProductsCarryoverXXX$21,369$73,453$64,16440+N/ASMulti
Bob SmithAccount 138Opportunity 138Energy DrinkUnknownRegion 2: WestVenue 138New YorkNY1100Sponsorship ProductsRenewalXXX$7,859$83,157$57,1230 - 5MultiSMulti
Bob SmithAccount 139Opportunity 139TravelUnknownRegion 2: WestVenue 139New YorkNY230100Sponsorship ProductsCarryoverXXX$17,357$101,002$82,22140+LifestyleSLifestyle
Bob SmithAccount 140Opportunity 140FoodUnknownRegion 1: EastVenue 140TampaFL55100Digital ProductsRenewalXXX$12,492$96,449$26,97940+MultiSMulti
Bob SmithAccount 141Opportunity 141TechnologyUnknownRegion 1: EastVenue 141TampaFL249100Digital ProductsCarryoverXXX$10,112$76,557$92,29840+EnterpriseSTech
Bob SmithAccount 142Opportunity 142AlcoholUnknownRegion 1: EastVenue 142TampaFL144100Digital ProductsRenewalXXX$62,251$111,068$24,51140+LifestyleSLifestyle
Bob SmithAccount 143Opportunity 143Energy DrinkUnknownRegion 1: EastVenue 143TampaFL230100Digital ProductsCarryoverXXX$54,520$102,905$31,97340+N/ASLifestyle
Bob SmithAccount 144Opportunity 144Financial ServicesUnknownRegion 1: EastVenue 144TampaFL230100Digital ProductsCarryoverXXX$86,803$8,869$118,66040+N/ASMulti
Bob SmithAccount 145Opportunity 145TechnologyUnknownRegion 1: EastVenue 145TampaFL61100Digital ProductsNewXXX$118,992$4,577$53,73840+TechSTech
Bob SmithAccount 146Opportunity 146Energy DrinkUnknownRegion 1: EastVenue 146TampaFL1100Digital ProductsRenewalXXX$58,141$75,398$70,2450 - 5MultiSMulti
Bob SmithAccount 147Opportunity 147AutomotiveUnknownRegion 1: EastVenue 147TampaFL81100Digital ProductsRenewalXXX$18,589$66,556$69,67340+MultiSAuto
Bob SmithAccount 148Opportunity 148TravelUnknownRegion 1: EastVenue 148TampaFL230100Digital ProductsCarryoverXXX$119,560$27,868$8,31240+LifestyleSLifestyle
Bob SmithAccount 149Opportunity 149AlcoholUnknownRegion 1: EastVenue 149TampaFL8585Digital ProductsRenewalXXX$50,713$78,221$11,10940+N/ASLifestyle
Bob SmithAccount 150Opportunity 150AlcoholUnknownRegion 1: EastVenue 150TampaFL8585Digital ProductsRenewalXXX$89,270$33,972$108,33240+N/ASLifestyle
Bob SmithAccount 151Opportunity 151AlcoholUnknownRegion 1: EastVenue 151TampaFL8585Digital ProductsRenewalXXX$51,667$55,737$70,27140+N/ASLifestyle
Bob SmithAccount 152Opportunity 152AlcoholUnknownRegion 2: WestVenue 152San FranciscoCA144100Digital ProductsRenewalXXX$26,202$79,538$120,73840+LifestyleSLifestyle
Bob SmithAccount 153Opportunity 153Energy DrinkUnknownRegion 2: WestVenue 153San FranciscoCA230100Digital ProductsCarryoverXXX$99,693$56,440$40,57940+N/ASLifestyle
Bob SmithAccount 154Opportunity 154Financial ServicesUnknownRegion 2: WestVenue 154San FranciscoCA230100Digital ProductsCarryoverXXX$17,758$37,946$43,10740+N/ASMulti

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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