VBA to create new sheet with file name using save

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?

Answer 1

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