I have an invoice template with a data validation dropdown in Sheet1!A10 with the list location in Sheet3!B2:B4.
I want to return the value in the Sheet3!A2:A4 based on the dropdown selection (Sheet3!B2:B4) to save a copy of the file with the offset values from the dropdown.
Sub FileSaveAs()
Dim custlist As String
Dim custname As String
Dim path As String
Dim filename As String
custlist = Sheets(1).DropDowns(Range("A10:C10"))
custname = Application.WorksheetFunction.VLookup(custlist, Sheet3.Range("a:b"), 1, False)
path = "C:\Users\files"
filename = custname
Sheet1.Copy
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs filename:=path & filename, FileFormat:=51
.Close
End With
End Sub
How do I set the the value of custname
based on the dropdown selection custlist
?
Microsoft documentation:
Option Explicit
Sub FileSaveAs()
Dim custList As String
Dim custName As String
Dim sPath As String
custList = Sheet1.Range("A10").Value
sPath = "C:\Users\files\"
If Len(custList) > 0 Then
For Each c In Sheet3.Range("B2:B4")
If StrComp(c.Value, custList, vbTextCompare) = 0 Then
custName = c.Offset(0, -1).Value
End If
Next
End If
If Len(custList) > 0 Then
Sheet1.Copy
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs filename:=sPath & custName, FileFormat:=51
.Close
End With
End If
End Sub