VBA Help on drop down list

[Owl]

Hello!
I have a set of data which I need macro to help me to filter the data base on Vendor (at cell A36).

Each time when I have the set of data, the list of vendors will be different.
I need to macro to auto filter the first vendor code in the dropdown list, then do a summation of the value base of document currency, auto save in new worksheet.
Then, the macro will repeat the same process for 2nd/3rd etc vendor code in the drop down list.

30247

I tried using below VBA but unable to work:
Range("A36") = Range("Vendor").Cells(1, 1)

[Aussiebear]

Can this be done with a pivot table instead of a macro?

[arnelgp]

or using power query?

[snb]

A dynamic Table suffices.

[Owl]

Good idea! I think can do pivot table too :)

But I still need macro to do the job to select vendor from dropdown list, then auto save the excel or pdf.
I need to send the list to different vendors so I cannot send them the whole excel with whole list of different vendors inside.

the list will be generated monthly, hence the list of vendors will be different.

[Aussiebear]

Since I don't have your workbook ( and images are nearly useless), you could start with this to autofilter



Sub Filter_Vendors()
Worksheets("Sheet1").Range("A1").Autofilter Field:=1,Criteria1:="Vendor Name"
End Sub


Change, the sheet name to suit, and since Field is the column number ( and the image shows that Vendor Name resides in a column other than Column A) that you want to search on. Simply create a drop down list based on the data in Column "Vendor Name" and link it to a cell. Then we can possibly change the criteria1 value to reflect the linked cell.

[Owl]

I have attached the excel which is in pivot table format.
I require the macro to filter by vendor name... eg. filter by 1st vendor, then un-filter 1st vendor and filter the 2nd vendor.... etc

Can you please help me?

[arnelgp]

you can just add a Slicer to your pivot table, then you can filter by Vendor name.
click on the rightmost top of the slicer will remove the filter.

you Google more about Slicer if you are not familiar with it.

[Aussiebear]

Oh trust you arnelgp to make some common sense of it all..... here I was thinking, a couple or six of the current American administration plus some of the outgoing British Administration and some obscure VBA and bingo... this could be a 14 day mystery tour.

[georgiboy]

You don't want anything to do with British at the moment... we are currently the joke of all jokes on an international scale :banghead:

[p45cal]

On sheet Sheet1 of the attached, a button Create pdfs at cell F3.
What the macro does is the equivalent of:
1. moving the Vendor Name item from the Rows section of the pivot to the Page (Filter) section (if it's not already there).
2. executing Show Report Filter Pages:
30266
3. on each new sheet:
a) autofitting the columns
b) saving as pdf
c) deleting the sheet

Note that if you don't want pdfs for certain vendors (eg. (blank)) then you can filter those out of the Filter (Page) field before you click the button.

The code (including notes in the comments):
Sub blah()
'make a list of existing sheets:
ReDim Sheetnames(1 To Sheets.Count)
For i = 1 To Sheets.Count
Sheetnames(i) = Sheets(i).Name
Next i
'move the vendor to the pages (Filters) section of the pivot:
With Sheets("Sheet1").PivotTables(1)
With .PivotFields("Vendor Name")
.Orientation = xlPageField
.Position = 1
End With

.ShowPages PageField:="Vendor Name" ' creates new sheet for each vendor
' .SaveData = False 'this will not save pivot source data with the file; important if you want to hide other vendors' data from saved excel workbooks, doesn't apply to saving pdf files.
End With
For Each sht In Sheets 'run through the (new) sheets:
x = Application.Match(sht.Name, Sheetnames, 0)
If IsError(x) Then ' it's a new sheet
fname = ThisWorkbook.Path & Application.PathSeparator & sht.Name & " listing_test.pdf" 'be more imaginative here!
sht.Cells.EntireColumn.AutoFit 'make sure column widths are big enough to see all content
'save sheet as you want; here it's a pdf file. Will overwrite existing files of the same name:
sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Application.DisplayAlerts = False: sht.Delete: Application.DisplayAlerts = True 'disable this line if you want to keep the new sheet
End If
Next sht
End Sub