Dynamic drop down list using VBA and then copy data to another workbook

[Exceldud]

I am new to this forum as well as new to VBA - so please be gentle

I have two workbooks (RawData and Dashboard). In RawData workbook, the data is in the RawSales worksheet. I want to see if it is possible to do the following using macros:
1. Create a dynamic drop downlist for unique customers (Column B)
2. User selects one of the customers from the list and then the macro copies all the relevant rows to the Dashboard.xlsm workbook and puts them in SalesOrder worksheet. The macro renames the file (CustomerName_Dashboard_TodayDate.xlsm) and saves the Dashboard in the same folder. The macro closes the RawData workbook and leaves the new Dashboard file open.

Any help would be appreciated.

 

[GlennUK]

So, basically, you are saying that you've achieved no progress on this - tried nothing - attempted nothing - is that right?

 

[Exceldud]

Hi Glenn,

Some progress has been made since my posting. I have created a macro that seems to be working fine for moving filtered data to another workbook and naming the worksheet according to the variable that I had selected. This only piece of code that I could not figure out was how to create a dynamic drop down list. For now I have down a manual workaround where I have manually created a drop down list. From the list I can select the Customer Name value (In my case from cell H6)

Sub moveData()
Application.ScreenUpdating = False
Dim x As Range
Dim rng As Range
Dim last As Long
Dim sht, shtButtons, CustName, PathOnly, destSht, newFile As String
Dim newBook As Excel.Workbook
Dim Workbk As Excel.Workbook


PathOnly = ThisWorkbook.Path
newFile = "SalesDashboard.xlsm"

'Specify sheet name in which the data is stored
sht = "Sales"
shtButton = "Buttons"

'Specify destination sheet name
destSht = "SalesOrder"

Set newBook = Workbooks.Open(PathOnly & "\" & newFile)
newBook.Activate
newBook.Worksheets(destSht).Activate
Selection.Delete Shift:=xlUp

'Workbook where VBA code resides
Set Workbk = ThisWorkbook
Workbk.Activate

With Workbk.Sheets(shtButton)
Set x = .Range("H6")
End With

'Filter based on Customer Name in Column B
last = Workbk.Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row

With Workbk.Sheets(sht)
Set rng = .Range("A1:T" & last)
End With

With rng
.AutoFilter
.AutoFilter Field:=2, Criteria1:=x.Value
.SpecialCells(xlCellTypeVisible).Copy
newBook.Activate
newBook.Worksheets(destSht).Activate
Worksheets(destSht).Range("A1").Select
ActiveSheet.Paste
End With

ActiveWorkbook.SaveAs Filename:=(PathOnly & "\" & x & "_Dashboard_" & Format(Now(), "DD-MMM-YYYY hh mm AMPM") & ".xlsm")


' Turn off filter
Workbk.Sheets(sht).AutoFilterMode = False

With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub

 

[kevin9999]

You don't need VBA to create a dropdown list of unique customer names. The following demonstrates how it can be done - you'll just need to adjust the range references in the formulas to suit your actual layout.

Book1
	A	B	C	D	E
1	Customer List		Unique List		Dropdown
2	A. Customer		A. Customer		A. Customer
3	A. Customer		B. Customer
4	A. Customer		C. Customer
5	B. Customer		D. Customer
6	B. Customer		E. Customer
7	B. Customer
8	C. Customer
9	C. Customer
10	A. Customer
11	A. Customer
12	A. Customer
13	D. Customer
14	E. Customer
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=UNIQUE(FILTER(A2:A30,A2:A30<>""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=C2#

 

[Exceldud]

In your example, can UNIQUE reference another worksheet. For example,
=unique(filter(Sales!B2:B10000, Sales!B2:B10000<>""))

I get an error (That function isn't valid). The reason why I want the drop down on another worksheet is that the data from Sales worksheet is completely cleared before my other macros run.

 

[kevin9999]

In your example, can UNIQUE reference another worksheet. For example,
=unique(filter(Sales!B2:B10000, Sales!B2:B10000<>""))

I get an error (That function isn't valid). The reason why I want the drop down on another worksheet is that the data from Sales worksheet is completely cleared before my other macros run.

Sorry, I should have looked at the Office version you have before posting.

 

[Exceldud]

So Unique works only with 365? Any solution with 2016 Excel ?

 

[kevin9999]

So Unique works only with 365? Any solution with 2016 Excel ?

Unfortunately I think it will need to be a VBA solution.

 

[GlennUK]

To get a unique list you can run an Advanced Filter to another location, specifying unique output only.