Excel macro to create dropdown value of the sheet and values
Closed
svivek2687@
Posts
2
Registration date
Tuesday 16 June 2015
Status
Member
Last seen
7 July 2015
-
16 Jun 2015 à 02:49
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 18 Jun 2015 à 11:29
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 18 Jun 2015 à 11:29
Related:
- Excel macro drop down list
- Excel drop down list multiple columns - Guide
- Excel mod apk for pc - Download - Spreadsheets
- Excel online macros - Guide
- Counter strike 1.6 cheats list - Guide
- Epic games free games list - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
16 Jun 2015 à 11:45
16 Jun 2015 à 11:45
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 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
18 Jun 2015 à 11:29
18 Jun 2015 à 11:29
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
17 Jun 2015 à 02:30
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 :)