Excel macro to create dropdown value of the sheet and values

Closed
Report
Posts
3
Registration date
Tuesday June 16, 2015
Status
Member
Last seen
July 7, 2015
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,

I have spreadsheet containing multiple sheets and values in it. I would like to make a macro tool to create a dropdown button of the sheet and which list all the sheet names. By selecting sheet name from dropdown, it shoudl display all the values in that respective sheet. Added to it, the columns on respective sheet should be in dropdown, by selecting the column names the values should be filtered and viewed.
Please help in this.
Thanks in Advance

2 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Svivek,

To do that I used a combination of Data Validation, Named ranges, formula and code.
Let me explain and use the testbook below to further guide you.

I created 3 sheets: 1 for result and 2 for data.
On the first sheet:
Range A7:A8 contains the 2 data sheet names.
Cell A1 contains the dropdown list (data validation) to select the data sheets.

Then I selected the headers of the data sheets and gave them the name (Named Range) of the sheet.

On the first sheet:
Cell B1 contains dropdown list using the formula: =INDIRECT(A1)

Now implement the following code in the first sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range(Cells(1, "C"), Cells(Rows.Count, Columns.Count)).ClearContents
    Sheets(Target.Value).Range("A1").CurrentRegion.Copy Range("C1")
End If

If Not Intersect(Target, Range("B1")) Is Nothing Then
    Columns.Hidden = False
    For Each cell In Range(Cells(1, "C"), Cells(1, Columns.Count).End(xlToLeft))
        If cell.Value <> Target.Value Then cell.EntireColumn.Hidden = True
    Next cell
End If

End Sub


When you select a sheet in A1 then all the data from that sheet will be pasted, starting in C1. Selecting another sheets will clear the previous results before pasting the new ones.

When you select a column header in B1, all columns (starting from column C) will be hidden except the one selected in B1.

Here is the testbook to get a visual of the above:
http://ge.tt/48KN7SI2/v/0?c

Hopefully you like the results ...

Best regards,
Trowa
Posts
3
Registration date
Tuesday June 16, 2015
Status
Member
Last seen
July 7, 2015

Hello Trowa,

Thanks a lot for explaining in such a way. You are teaching me like a friend sitting near in school days. I appreaciate that, also I have few comments to add up in this. As per your adivse I have got the data by selecting sheet names, but can it be implemented in Form?
Having combobox for selecting sheets and when selecting it, the COMMON columns(first 3 columns) from all the sheets shoudl be displayed. From that Common fields, selecting data the filter showed display.

I am not sure I clearly explained. If u could help it, I will be thankful :)
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Svivek,

Thanks for appreciating the effort I put in.

Not sure what the added value of a combobox over a dropdown list is.

How about we add "All Sheets" to the list of sheets. When you select that the first 3 columns of all sheets (except the first) will be shown as result.

For that you will need to use the altered code below and change the dropdown list in B1 from =INDIRECT(A1) to =C1:Z1 (Choose the Z1 as close to the actual maximum column letter to prevent a large list of empty values in the DDL).

Let me add the testbook again:
http://ge.tt/6WdkxaI2/v/0?c

Will that work for you?

Best regards,
Trowa