VBA Macro to Generate New Sheet with Matched Rows

F

foodchemist

New Member
Joined
Jul 1, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I'm using a Dynamic report that is downloaded daily from a MySQL database and I'm wanting to be able to to sort items by their process step by being able to double-click on a cell with that text that would then generate a new sheet in the same workbook with all rows matching (some rows have two items that go together but might be at different processing steps, if so, leave the non-match part blank and still pull the other matching portion):

I already have a macro that sorts the information by date and generates a list on the right on side of how many items at a step:

Due DateSMART OrderMFG OrderShip CountryBox IDOptionCurrent StepBox RackBox Type2nd Box IDAntisense Step2nd Box RackNumber of RushesGardenProcessing
2021-05-21​
826370United KingdomXXPPL-123STD JuiceIn Wash495462CartonXXPPL-124Weigh49608312PickWeigh
2021-05-21​
826370United KingdomXXPPL-125STD JuiceWeigh496083CartonXXPPL-126In Wash49547255
2021-05-21​
826370United KingdomXXPPL-127STD JuiceWeigh496083CartonXXPPL-128In Wash494743GrowInspect
2021-06-16​
830958USAMAJES-196Rinse OnlyPick00
2021-06-24​
832081USAGOLFA-23STD JuiceIn Wash495620GrowGOLFA-24Pick496348BagIn Wash
2021-06-25​
832150832152JapanAPPLE-53Barrel AgePick05
2021-06-25​
832150832152JapanAPPLE-54Barrel AgePickGrowAPPLE-55PickWeighTaste Test
2021-06-28​
831677GermanyBERRY-19FermentWeigh496295Smash & Wash51
2021-06-28​
832573USALOGMC-32STD JuicePickLOGMC-33In Wash4956412nd WeighBarrel Test
2021-06-28​
832636832637CanadaXXPPL-204STD JuiceWashed496376StockXXPPL-205Juiced49625900
2021-06-29​
832201USASTRBY-12Barrel AgePickWashed
2021-06-29​
831856FranceOLEVO-55FermentTaste Test496343Smash & Wash1
2021-07-04​
832501USASPLCE-21STD JuiceWashed496359JuiceSPLCE-22Juiced495749Juiced
2021-07-15​
832501USASPLCE-23STD JuiceWashed496359JuiceSPLCE-24Juiced4958071

So for instance, if I double-click "Pick" which states there are five items that are in the "Pick" step of processing, the new sheet it would generate would be:

2021-06-16​
830958USAMAJES-196Rinse OnlyPick
2021-06-24​
832081USAGrowGOLFA-24Pick496348
2021-06-25​
832150832152JapanAPPLE-53Barrel AgePick
2021-06-25​
832150832152JapanAPPLE-54Barrel AgePickGrowAPPLE-55Pick
2021-06-28​
832573USALOGMC-32STD JuicePick
2021-06-29​
832201USASTRBY-12Barrel AgePick

So for Line 2, you see that GOLFA-23 is removed from the new sheet because it does not match (it is at the "In Wash" step) but I still need the main order info (2021-06-24, 832081, USA). So even if one of the two items match, I still need the rest of the row but I don't need the other item's info if it doesn't match the processing step.

Thanks for any help!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Hi and Welcome to MrExcel.

Try the following, you can double click on any cell in any column.

Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sh As Worksheet
  Application.ScreenUpdating = False
  Set sh = ActiveSheet
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  sh.Range("A1").AutoFilter Target.Column, Target.Value
  Sheets.Add , Sheets(Sheets.Count)
  sh.AutoFilter.Range.EntireRow.Copy ActiveSheet.Range("A1")
  sh.ShowAllData
End Sub
 
Upvote 0
Thanks for the welcome, Dante!

I tried the code but it's only pulling the first row where I double-click, not collecting all the matching rows into the new sheet?

I double-clicked "Pick" (Cell N2) and it generated the new sheet but fills it with only one item that doesn't match what I'm looking for when double-clicking.

Due DateSMART OrderMFG OrderShip CountryBox IDOptionCurrent StepBox RackBox Type2nd Box IDAntisense Step2nd Box RackNumber of RushesGardenProcessing
2021-05-21​
826370United KingdomXXPPL-123STD JuiceIn Wash495462CartonXXPPL-124Weigh4960831PickWeigh

Instead of what I'm looking for when I double-click "Pick" (Cell N2):

2021-06-16​
830958USAMAJES-196Rinse OnlyPick
2021-06-24​
832081USAGrowGOLFA-24Pick496348
2021-06-25​
832150832152JapanAPPLE-53Barrel AgePick
2021-06-25​
832150832152JapanAPPLE-54Barrel AgePickGrowAPPLE-55Pick
2021-06-28​
832573USALOGMC-32STD JuicePick
2021-06-29​
832201USASTRBY-12Barrel AgePick
 
Upvote 0
foodchemist said:
I double-click "Pick" (Cell N2)
Click to expand...

In column N you only have one record with "Pick", you must double click on a cell with "Pick" but in column "G", there you have 5 records.
Try again.

Dante Amor
AFGHMNO
1Due DateOptionCurrent StepBox RackNumber of RushesGardenProcessing
22021-05-21STD JuiceIn Wash49546212PickWeigh
32021-05-21STD JuiceWeigh49608355
42021-05-21STD JuiceWeigh496083GrowInspect
52021-06-16Rinse OnlyPick00
62021-06-24STD JuiceIn Wash495620BagIn Wash
72021-06-25Barrel AgePick05
82021-06-25Barrel AgePickWeighTaste Test
92021-06-28FermentWeigh49629551
102021-06-28STD JuicePick2nd WeighBarrel Test
112021-06-28STD JuiceWashed49637600
122021-06-29Barrel AgePickWashed
132021-06-29FermentTaste Test4963431
142021-07-04STD JuiceWashed496359Juiced
152021-07-15STD JuiceWashed4963591
Hoja7
 
Upvote 0
Ahh, I see now, Dante!

It's nearly exactly what I'm looking for - this is the result I'm getting now with your clarification:
Due DateSMART OrderMFG OrderShip CountryBox IDOptionCurrent StepBox RackBox Type2nd Box IDAntisense Step2nd Box RackNumber of RushesGardenProcessing
2021-06-16​
830958USAMAJES-196Rinse OnlyPick00
2021-06-25​
832150832152JapanAPPLE-53Barrel AgePick05
2021-06-25​
832150832152JapanAPPLE-54Barrel AgePickAPPLE-55PickWeighTaste Test
2021-06-28​
832573USALOGMC-32STD JuicePickLOGMC-33In Wash4956412nd WeighBarrel Test
2021-06-29​
832201USASTRBY-12Barrel AgePickWashed

Is there a way to ignore columns M, N, and O when copying to the new sheet? The macro is also grabbing cells Sheet1!J5-L5, is there a way this would just pop up as blank for that three cells? It doesn't seem to be grabbing Sheet1!J6-L6 either.

And is it possible to restrict the double-clicking to only the headers in Sheet1!N (like Sheet1!N2,N4,N6,N8,N10) and Sheet1!O (like Sheet1!O2,O4,O6,O8,O10,O12,O14)?
 
Upvote 0
I think I misunderstood.
Let's review. You want to double click on any of these cells: N2, N4, N6, N8, N10, O2, O4, O6, O8, O10, O12, O14
Should I look up the data in columns G and K?
and the matches copy only columns A to L?

Try this:

VBA Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("N2,N4,N6,N8,N10,O2,O4,O6,O8,O10,O12,O14")) Is Nothing Then
    Dim sh As Worksheet
    Dim i As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    
    Set sh = ActiveSheet
    Set rng = sh.Range("A1").Resize(1, 12)
    If sh.AutoFilterMode Then sh.AutoFilterMode = False
    
    For i = 1 To sh.Range("A" & Rows.Count).End(3).Row
      If Range("G" & i).Value = Target.Value Or Range("K" & i).Value = Target.Value Then
        Set rng = Union(rng, sh.Range("A" & i).Resize(1, 12))
      End If
    Next
    
    Sheets.Add , Sheets(Sheets.Count)
    rng.Copy ActiveSheet.Range("A1")
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    Cancel = True
  End If
End Sub
 
Upvote 0
Solution
Dante,

That seems to be the closest to the issue I was having. Thank you for the assistance!

Is there a way to inject that macro/code without having to right-click on the Worksheet tab since it is a newly generated report each day (to avoid having to copy and paste it over and over each time)?
 
Upvote 0
foodchemist said:
Is there a way to inject that macro/code without having to right-click on the Worksheet
Click to expand...

That's the short way.
The other way is to go to VBA with ALT + F11, select the sheet object with double click and replace the code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA Code for Macro - Search sheet for keywords and paste rows to new sheet
Replies
9
Views
3K
kevin9999
K

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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