[Solved] VBA for variable file name and CSV file save

[jeannieg80]

I am looking to create a macro to copy all cells from a tab and paste the values into a new workbook and save the result as a CSV file allowing the user to define the filename. I would like to define the path to the folder to save into. Thank you for the help.

 

[Mashhura Jahan]

I am looking to create a macro to copy all cells from a tab and paste the values into a new workbook and save the result as a CSV file allowing the user to define the filename. I would like to define the path to the folder to save into. Thank you for the help.

Hey jeannieg80,

Thank you for your query. First, open a module in the workbook from where you want to copy the data. Then write the following code in that module.

Sub Copy_and_Save_as_CSV()

Dim my_Filename As String
Dim my_WB As Workbook

Application.DisplayAlerts = False

On Error GoTo err

Filename = InputBox("Give File Name")
my_Filename = ThisWorkbook.Path & "\" & Filename & ".csv"

ThisWorkbook.Sheets("Sheet1").Activate
ActiveSheet.Copy
Set my_WB = ActiveWorkbook

With my_WB
.SaveAs Filename:=my_Filename, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With

err:
Application.DisplayAlerts = True

End Sub

Now, simply run the code and give the file name in the input box. I hope this will solve your problem. I have also attached the Excel file for your convenience. Let us know if you face any issues. Thank you.

Regards
Mashhura Jahan
ExcelDemy