Changing path location via dropdown

[billy1989]

Hi all,

The below code works great, but I was wondering how to tweak it slightly - I have the path set, but was looking to see if I could set a dropdown for months, and have it set the path as what it currently is, but point to a folder within there corresponding to the month?

eg path is "D:\Carcharias Creative\CSVs", I'd like to select a dropdown when naming the file for months, and have it save in "D:\Carcharias Creative\CSVs\August" for example

Sub ExportAsCSV()

Dim MyFileName As String
Dim Item As String
Dim Path As String
Dim CurrentWB As Workbook, TempWB As Workbook
Dim myrangeNA As Range
Dim myRangeCSV As Range
Path = "D:\Carcharias Creative\CSVs"

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.Worksheets("CSV").Activate
Set myrangeNA = Application.InputBox(prompt:="Select a range to copy (include header row).", Type:=8)
Item = InputBox("Please input filename", "Filename")

Set TempWB = Application.Workbooks.Add(1)
myrangeNA.Copy Destination:=TempWB.Worksheets("Sheet1").Range("A1")

MyFileName = Path & "\" & Item & ".csv"

Application.DisplayAlerts = True
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSVUTF8, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

 

[John_w]

I'd like to select a dropdown when naming the file for months, and have it save in "D:\Carcharias Creative\CSVs\August" for example

You could use a Data Validation In-cell dropdown with a list of the month names:

and read the cell's value in your code.

For example, if the DV cell is cell B2 on Sheet2, add this to your code:

    Path = Path & "\" & Worksheets("Sheet2").Range("B2").Value
    If Dir(Path, vbDirectory) = vbNullString Then MkDir Path

 

[billy1989]

You could use a Data Validation In-cell dropdown with a list of the month names:

View attachment 94547

and read the cell's value in your code.

For example, if the DV cell is cell B2 on Sheet2, add this to your code:

    Path = Path & "\" & Worksheets("Sheet2").Range("B2").Value
    If Dir(Path, vbDirectory) = vbNullString Then MkDir Path

Perfect, thank you!