Adding popup to select folder for the exported csv sheets

[linister]

Hi,

I created a button that exports all sheets to a sperate excel csv workbook, however what I want is when clicking on the button to get a popup that asks me where do i want to save those workbooks.

Here's the macro that I currently have:

Sub Button1_Click()
        Dim xWs As Worksheet
        Dim xcsvFile As String
        For Each xWs In Application.ActiveWorkbook.Worksheets
            xWs.Copy
            xcsvFile = "C:\Users\linja\Desktop\" & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
            FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        Next
    End Sub

I don't want to write the path of where to export the xcsvFile manually, i want to get the popup that allows me to choose the export folder.

Thanks in advance!

 

[Fluff]

How about

Sub Button1_Click()
        Dim xWs As Worksheet
        Dim xcsvFile As String
        Dim Pth As String
        
      With Application.FileDialog(4)
         .AllowMultiSelect = False
         If .Show Then Pth = .SelectedItems(1)
      End With

        For Each xWs In Application.ActiveWorkbook.Worksheets
            xWs.Copy
            xcsvFile = Pth & "\" & xWs.Name & ".csv"
            Application.ActiveWorkbook.SaveAs fileName:=xcsvFile, _
            FileFormat:=xlCSV, CreateBackup:=False
            Application.ActiveWorkbook.Saved = True
            Application.ActiveWorkbook.Close
        Next
    End Sub

 

[linister]

Sub Button1_Click() Dim xWs As Worksheet Dim xcsvFile As String Dim Pth As String With Application.FileDialog(4) .AllowMultiSelect = False If .Show Then Pth = .SelectedItems(1) End With For Each xWs In Application.ActiveWorkbook.Worksheets xWs.Copy xcsvFile = Pth & "\" & xWs.Name & ".csv" Application.ActiveWorkbook.SaveAs fileName:=xcsvFile, _ FileFormat:=xlCSV, CreateBackup:=False Application.ActiveWorkbook.Saved = True Application.ActiveWorkbook.Close Next End Sub

Yess!!! works great!!! thanks alot Fluff you're the best

 

[Fluff]

You're welcome & thanks for the feedback.