VBA to create new sheet with file name using save dialogue box and save as CSV format

Question

My requirement is that I am having a sheet with some data and a button which has to perform below said task.

Task is that once button is clicked it has to ask for file name and location to be save to the new sheet in csv format and the data in the active sheet has to be copied and pasted in a new sheet and name of new sheet should be the name given for file to save in CSV format.

Can anyone please help on this?

do a search of SO, this type of question has been answered many times. — Sorceri, Apr 12 '17 at 18:21
I wont because that is me doing the search for you, the point is that you could have had an answer already if you just do a search, top of the page, search vba save sheet CSV, literally the first hit will give you an answer. — Sorceri, Apr 12 '17 at 18:26

score 1 · Accepted Answer · edited May 23 '17 at 10:31

This should do the trick, edit to taste. Save the macro to a new module, then add a button Developer Tab > Insert > Button. Assign the button to this macro.

Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook

'Change the path, must end with \
SaveToDirectory = "C:\Users\username\Documents\test\"
For Each WS In ThisWorkbook.Worksheets
    Sheets(WS.Name).Copy
    ActiveWorkbook.SaveAs Filename:=SaveToDirectory & ThisWorkbook.Name & "-" & WS.Name & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close savechanges:=False
    ThisWorkbook.Activate
Next
'edit/remove the for loop to suit
End Sub

Credit where credit is due. Adapted from this answer: Saving excel worksheet to CSV files with filename+worksheet name using VB

VBA to create new sheet with file name using save dialogue box and save as CSV format

1 Answers1

Linked