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?
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