Excel Macro Help- need a macro that runs based on a drop-down data validation field

You did not say where and how you want the report of the selection to be like when the user select AMF you say:
I would like only the names with 1 as the "org code" (see Data1 and Data2 worksheets) to report.
>> To report where and how in what format ? In hte first sheet in Col C ??? not clear.

Then when you say Col F is not always the same ... that's not a problem does the header like the first row of that col is the same so we can locate it ???
gowflow

>> To report where and how in what format ? In hte first sheet in Col C ??? not clear.
I would just like the Macro to Sort/Only Display the corresponding information on the worksheets Data1 and Data2. (please note the request to have Data 1 and Data 2 hidden until the macro runs

Basically: Select Organization, Select Compliance, Run Macro, have Data1, Data 2 unhide and only have the pertenant information displayed based on the selections.

Then when you say Col F is not always the same ... that's not a problem does the header like the first row of that col is the same so we can locate it ???

The title of the column header is always "Times Completed" if you can locate that way. It is always in Row 1.

Basically: Select Organization, Select Compliance, Run Macro, have Data1, Data 2 unhide and only have the pertenant information displayed based on the selections.

>>> Data1 and Data2 are not hidden !!! you want them hidden by default and when the user selects they got unhidden ??

that's all I need for now.
gowflow

Yes, they will be hidden in the final workbook that I add the finished macro to. So esentially the Macro will have to have an "Unhide" on all applicable worksheets after sorting.

ok chk out the file and let me know if this is what you want. Make sure your macroes are enabled.

I only changed a small thing I inverted Compliant and non-compliant in the Menu sheet that all.

gowflow
MacroHelpExpertsExchange.xlsm

Yes, thats exactly the result I'm looking for. However, you did miss two points that I made:

the real-data's worksheets are not called "data 1" and "data 2" the worksheet names change from the source database on each export. So the Macro has to run on ALL worksheets in the workbook.

Meaning that in the non-dummy data the worksheets export as different names each time so I just need the marco to run on all active sheets.

no problem just 1 question:
will the workbook always have 4 sheets ? Menu SelectPMO and the 2 other sheets ?
gowflow

there could be Data 1-5 or even 1-10 depending on how someone exports from the source database.

ok fine but for sure you will have Menu and SelectPMO at least !!!! as they are a reference. I noticed also a small bug that has been addressed.

This version will go thru as many as there is worksheets up to the maximum that Excel allows and will apply the criterias you advised.

Pls check it thourouly and let me know.
gowflow
MacroHelpExpertsExchange-AllShee.xlsm

Okay, everything seems to work correctly. Now I have to try to apply this to the Real-Data....

Looking at the Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
'Dim WSData1 As Worksheet
'Dim WSData2 As Worksheet
Dim WSActive As Worksheet
Dim WSData As Worksheet
Dim Itm As Long
Dim FilterOff As Boolean
Dim c As Range

Set WS = Sheets("Menu")
Set WSActive = ActiveSheet
'Set WSData1 = Sheets("Data1")
'Set WSData2 = Sheets("Data2")
    
If WSActive.Range("A2").Value <> "" And WSActive.Range("B2").Value <> "" Then
    
    If WSActive.Range("A2").Value <> "" Then
        Set c = WS.Range("A:A").Find(What:=WSActive.Range("A2").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            For Each WSData In ActiveWorkbook.Worksheets
                If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
                    Filter WSData, 2, c.Row, FilterOff
                    'Filter WSData1, 2, c.Row, FilterOff
                    'Filter WSData2, 2, c.Row, FilterOff
                End If
            Next WSData
        End If
    
    End If

    
    If WSActive.Range("B2").Value <> "" Then
        Set c = WS.Range("B:B").Find(What:=WSActive.Range("B2").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            If c.Row = 1 Then Itm = 0
            If c.Row = 2 Then Itm = 1
            For Each WSData In ActiveWorkbook.Worksheets
                If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
                    Filter WSData, 6, Itm, FilterOff
                    'Filter WSData1, 6, Itm, FilterOff
                    'Filter WSData2, 6, Itm, FilterOff
                End If
            Next WSData
        End If
    Else
        If WSActive.Range("A2").Value = "" And WSActive.Range("B2").Value = "" Then
            FilterOff = True
        End If
        For Each WSData In ActiveWorkbook.Worksheets
            If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
                Filter WSData, 6, -1, FilterOff
                'Filter WSData1, 6, -1, FilterOff
                'Filter WSData2, 6, -1, FilterOff
            End If
        Next WSData
    End If

Else
    FilterOff = True
    For Each WSData In ActiveWorkbook.Worksheets
        If WSData.Name <> "Menu" And WSData.Name <> "SelectPMO" Then
            Filter WSData, 2, -1, FilterOff
            'Filter WSData1, 2, -1, FilterOff
            'Filter WSData2, 2, -1, FilterOff
        End If
    Next WSData
    
End If

End Sub

Sub Filter(WS As Worksheet, Col As Long, Item As Long, FltrOff As Boolean)
Dim c As Range

If Col = 6 Then
    Set c = WS.Range("1:1").Find(What:="Times Completed", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        Col = c.Column
    End If
    
End If
If Item <> -1 Then
    WS.Range(WS.Cells(2, Col), WS.Cells(2, Col)).AutoFilter field:=Col, Criteria1:=Item
Else
    WS.Range(WS.Cells(2, Col), WS.Cells(2, Col)).AutoFilter field:=Col
End If

If FltrOff Then WS.AutoFilterMode = False

End Sub

Select allOpen in new window

What lines in that code are stipulating that AMF = 1 BO = 2 BPFTI = 3? Sorry, I thought the code would be a little easier to understand....

no problem I did not code anything it is all flexible and that's my way of doing.
If you look at sheet Menu you have your col A and it is the row coresponding to the data that is taken to figure out the 1 and 2 etc ...

So as long as you follow this principle no matter how many names you put it will be fine as long as you use the row as code to the item you create.

Is this fine for you ?>
gowflow

Did you implement in your work did the solution work for you ? any help needed pls do not hesitate to let me know.
gowflow

no problem I did not code anything it is all flexible and that's my way of doing.
If you look at sheet Menu you have your col A and it is the row coresponding to the data that is taken to figure out the 1 and 2 etc ...

So as long as you follow this principle no matter how many names you put it will be fine as long as you use the row as code to the item you create.

Is this fine for you ?>

I see what you did, no this will not work, this is because in the real data "AMF" has to equal several numbers not just "1" or "2".

The rules for the real data are as follows:
AMF Equals:
2804040000000000  
2804040001000000  
2804040003000000
2804040004000000
2804040005000000
2804040006000000

BO Equals:
2804000200000000

BPFTI Equals:
2804050000000000
2804050001000000
2804050001010000
2804050001020000
2804050002000000
2804050002040000
2804050002050000
2804050002060000
2804050003000000
2804050003030000
2804050003040000
2804050004000000
2804050004010000
2804050004020000
2804050004030000

EMO Equals:
2804010000000000
2804010001000000
2804010001010000
2804010001020000
2804010001030000
2804010004000000
2804010004010000
2804010004020000
2804010004030000
2804010005000000

FOF Equals:
2804060000000000
2804060002000000
2804060002010000
2804060002040000
2804060002050000
2804060002060000
2804060002070000
2804060003000000

MSF Equals:
2804030000000000
2804030002000000
2804030002010000
2804030002020000
2804030004000000
2804030004010000
2804030004020000
2804030004030000
2804030004040000
2804030006000000

ORS Equals:
2804020000000000
2804020001000000
2804020002000000
2804020002010000
2804020002020000
2804020002030000
2804020002040000

XO Equals:
2804000100000000

XD Equals:
2804000000000000

Sorry I didn't make this clear initally, my thought was that you would code AMF="1" into the macro so that I could just modfiy the macro for the production version.

Again no need to code. here is my suggestion in sheet Menu like in the attached file we can use Col C to be min code number and Col D to be Max Code number so the C-D would be the range of codes for each item in Col A. This solution is flexible for you as you can add codes ... check how I filled them (took it from what you posted) and if it is ok then I will modify the code to work with these codes.

Note: this file is only to look at sheet Menu nothing has been modifed in the code.
Let me know.
gowflow
Copy-of-MacroHelpExpertsExchange.xlsm

Yes, that will work just as well!

Try this version of the file does it meet the new requirement ?
gowflow
MacroHelpExpertsExchange-AllShee.xlsm

Doesn't seem to be working properly, I've tried to code the frist 3 rows in Data 1 as AMF and the macro does not seem to filter them properlly.
MacroHelpExpertsExchange-AllShee.xlsm

I think this should do it. I changed the formating for both C and D columns in Menu to be Number format with 0 decimals. You will need to format all the Code columns in your workbook to be the same
Number
0 Decimals.

Let me know.
gowflow
MacroHelpEE-NEW.xlsm

That's understandable.
Please see the attached for the last changes, I tried to change around your code to accomodate the headers in the real version but I failed. Your code is unaltered here. Please reposition where the macro runs the filter.

Also, please add in something to the macro that unhides the sheets which it filtered after it has completed running.
MacroHelpEE-NEW.xlsm

Also, please add in something to the macro that unhides the sheets which it filtered after it has completed running.
>>> What do you mean by that ???? once both fields are filled it runs the filter and if you want to remove the filter just delete 1 of the fields data either Col A or Col B

working on hte other issue. Menatime how did you paste the data ? still text this is why it is not working well !!! you need to copy/paste from Menu the item that you want and then modify it in the sheet.

This is a problem with Excel as after 16 digits all numbers turn to Exponetial so we hv to twaist the things to get around that.

Anyway I got an error here and checking it.

LAST BUT NOT LEAST:
If you had supplied the final format since the beginning we would not hv waisted all this time. I give solutions that works coz I test them thouroughly ! so if you give me part of the story and then you want to change then it does not work with you then we are both waisting time.

gowflow

LAST BUT NOT LEAST

Yes, sorry about that, I really thought the macro would be easier for me to change around then it ended up being. I apologize for that immensely.

Menatime how did you paste the data ?

I used the "Move and Copy" feature and just moved the worksheet from the real-report to your workbook.

Regarding the numbers, in the real-report, sometimes the formatting on the Org. Code field is "General", sometimes it's "Number" and sometimes it's "Text". I am comfortable with changing the formatting on the fields to "Number" (no decimals) but that doesn't change over all of the cells to actual numbers... why is this?

What do you mean by that ???? once both fields are filled it runs the filter and if you want to remove the filter just delete 1 of the fields data either Col A or Col B

When I send out the the final version of this workbook for distribution, worksheets Dummy 1 and Dummy 2 .... 3, 4, 5, 6, 7 etc... will be hidden worksheets. I want the macro to unhide the worksheets after selections have been made in "Select PMO" and "Select Compliance"

Try this version
gowflow
MacroHelpEE-NEWUser.xlsm

When I send out the the final version of this workbook for distribution, worksheets Dummy 1 and Dummy 2 .... 3, 4, 5, 6, 7 etc... will be hidden worksheets. I want the macro to unhide the worksheets after selections have been made in "Select PMO" and "Select Compliance"

>> Well this too I did not know. So to cut it short test the version I posted then tell how it is and also tell me if there is a password to the sheets.
will wait for your feedback before making any further change.

gowflow

That version works perfectly. Thank You. How did you manage to change over the Org. Code cells which were formatted as text to numbers so quickly?

Can this be include into the macro so that I do not have to reformat the original report?

You should ask for a Million Dollar !!!

Well the version I send you I did manually, but figured out that you may hv zillions of records so I included a macro that will clean the whole thing out.

Pls follow these basics:
1) When there is code in a module, leave it in a module don't copy it onto a worksheet as it may mix the references.
2) If there is a button on a worksheet that calls a macro and you don't want that button then simply delete the button but before that remember what is the macro that is lying under that button so you can activate it manually.

3) To implement your production:

3.1) Open this workbook and DO NOT ACTIVATE MACROES.
3.2) do as you did before and move all your data sheets into this workbook and once finished save the workbook and close it.
3.3) Make a backup copy of the workbook and give it a new name, case when you run the macro if something goes wild then you hv your workbook saved under the new format and intact.
3.4) Activate Macroes for a copy of the new workbook (I say Activate as usually for security reasons your macroes should be all disabled with notification)
3.5) Run the button in sheet SelectPMO it will format all your 'pertinent' worksheets in the workbook for the Columns Org Code.
3.6) Test the workbook with selections.

Ket me know your feedback.


1 Last Issue before I send the final version, it is still not clear to me the status of the worksheets (hid/unhide)
My understanding correct me if I am wrong when you open the workbook all sheets are hidden Except Menu and SelectPMO. Is that correct ? and when you make a selection you want to unhide the data sheets ?

So if this is what you eant you need to tell me what sheet to unhide ? Only the ones that contain results or all of them ? and when to hide them again ?? as this is not clear to me.

gowflow

Pls answer my questions for the Hide/Unhide worksheets so I can finalise it and send it to you.
gowflow

3) To implement your production:

3.1) Open this workbook and DO NOT ACTIVATE MACROES.

You forgot the attachment

Well if you read the whole thing you will see that I am waiting for your answer before attaching the workbook !!!!

gowflow

Gotcha, all of the sheets, even the ones without results.

I REiterate the questions:

==============
1 Last Issue before I send the final version, it is still not clear to me the status of the worksheets (hid/unhide)
My understanding correct me if I am wrong when you open the workbook all sheets are hidden Except Menu and SelectPMO. Is that correct ? and when you make a selection you want to unhide the data sheets ?

So if this is what you eant you need to tell me what sheet to unhide ? Only the ones that contain results or all of them ? and when to hide them again ?? as this is not clear to me.
===============

Pls answer each and every question.
gowflow

I'd like only SelectPMO to be visible, but if "Menu" has to be visable, i'm okay with that.

Sheets never need to be hidden again after inital run.

intiial run is always relative !!! so what is the use of hiding them if after the first run you don't want them to be hidden.

It seems you cannot envision the end result. I will help you.
What you want is when you start the workbook to hv all sheets hidden (menu should be unhidden to allow to add data if and when you need, unless it is final and finshed and no need to add anymore data to this sheet then we can permanantly hide it. YOU TELL ME.

So after the workbook started all is hidden except SelectPMO then when selection are performed, then all sheets unhidden up until you close the workbook. When you close the workbook they will become again all hidden. For the next opening.

Is that what you want ???
gowflow

You'll never need to modify Menu.

Exactly what I want.

Here it is
gowflow
MacroHelpEE-Final.xlsm

Thank You gowflow, I've done my acceptance based testing and discovered one small-glitch.

There is some data input inside of row 6 in the header of the real data that I have removed for our build-out. When there is data input into row 6 the filter does not locate correctly. Is there anything you can do about this?

See worksheet "Testing 1"
EE-Glitch.xlsm

One more Glitch noticed, if there is any data in Column C below row 7 the filter places itself in row 8 rather than row7

Thank you for your patience with me on something that required a lot of reiteration. You solution is excellent and meets all of my requirements.

I'm gald it finally met what you wanted and trust me my satissfaction is to get the user get EXACTLY what he wants and nothing less.
gowflow