Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]

Question

I am very new with VB coding, I am trying to save multiple excel file worksheets to csv, I don't know to do this for multiple sheets, but I found a way to do for single file. I have found code on this site which are very useful for what I am trying to do, only problem is the files are saved with the worksheet name but I am trying to save them with the original file and worksheet name such as filename_worksheet name, I tried to do that myself but keep getting error, could you please advise what I am doing wrong?

The code I am using is as follows:

   Public 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    SaveToDirectory = "H:\test\"    For Each WS In ThisWorkbook.Worksheets    WS.SaveAs SaveToDirectory & WS.Name, xlCSV    Next  Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat Application.DisplayAlerts = True ' Temporarily turn alerts off to prevent the user being prompted '  about overwriting the original file.  End Sub  

Answer 1

I think this is what you want...

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 SaveToDirectory = "H:\test\"  For Each WS In Application.ActiveWorkbook.Worksheets     WS.SaveAs SaveToDirectory & WS.Name, xlCSV Next  Application.DisplayAlerts = False ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat Application.DisplayAlerts = True ' Temporarily turn alerts off to prevent the user being prompted '  about overwriting the original file.  End Sub