Excel macro to create dropdown value of the sheet and values
Closed
svivek2687@
Posts
3
Registration date
Tuesday June 16, 2015
Status
Member
Last seen
July 7, 2015
-
Jun 16, 2015 at 02:49 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 18, 2015 at 11:29 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 18, 2015 at 11:29 AM
Related:
- Excel macro to create dropdown value of the sheet and values
- Create skype account with gmail - Guide
- Excel macro to create new sheet based on value in cells - Guide
- How to take screenshot of excel sheet - Guide
- Spell number in excel without macro - Guide
- Mark sheet in excel - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 16, 2015 at 11:45 AM
Jun 16, 2015 at 11:45 AM
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:
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 18, 2015 at 11:29 AM
Jun 18, 2015 at 11:29 AM
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
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
Jun 17, 2015 at 02:30 AM
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 :)