On export option where you want to save
 

On the macro below where I export the information from excel to .CSV
saves automatically to a predetermined destination. How can I remove
that destination and have an option of an MsgBox ask me the location to
save the .CSV file?

Sub ExportAsCSV()

Dim myRange As Range
Dim curWks As Worksheet
Dim tmpWks As Worksheet

Set curWks = ActiveSheet

With curWks
.Range("B3:L25").AutoFilter field:=1, Criteria1:="<"
If .AutoFilter.Range.Cells.Count 1 Then
Set tmpWks = Workbooks.Add(1).Worksheets(1)
.AutoFilter.Range.EntireRow.Copy _
Destination:=tmpWks.Range("a1")
Application.DisplayAlerts = False
tmpWks.Parent.SaveAs _
Filename:="c:\test.csv", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
tmpWks.Parent.Close savechanges:=False
End If
.AutoFilter.Range.AutoFilter
End With

End Sub

Thank you,

Muaitai

On export option where you want to save
 

Look at Application.GetSaveAsFilename

NickHK

"Muaitai" wrote in message
oups.com...
On the macro below where I export the information from excel to .CSV
saves automatically to a predetermined destination. How can I remove
that destination and have an option of an MsgBox ask me the location to
save the .CSV file?

Sub ExportAsCSV()

Dim myRange As Range
Dim curWks As Worksheet
Dim tmpWks As Worksheet

Set curWks = ActiveSheet

With curWks
.Range("B3:L25").AutoFilter field:=1, Criteria1:="<"
If .AutoFilter.Range.Cells.Count 1 Then
Set tmpWks = Workbooks.Add(1).Worksheets(1)
.AutoFilter.Range.EntireRow.Copy _
Destination:=tmpWks.Range("a1")
Application.DisplayAlerts = False
tmpWks.Parent.SaveAs _
Filename:="c:\test.csv", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
tmpWks.Parent.Close savechanges:=False
End If
.AutoFilter.Range.AutoFilter
End With

End Sub

Thank you,

Muaitai