Export multiple worksheets as CSV with worksheet name

[jpsimmon]

What do I add to this code to export my worksheets with their worksheet name?
Currently it shoots them out as Sheet1, Sheet2, Sheet3 etc...

Sub ExpCsv()Dim i As Integer


For i = 1 To 3


Sheets(i).Activate
Range("A1:W645").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="S:\Com\Monthly\Test\" & Sheets(i).Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True


Next
End Sub

Thanks,

 

[Joe4]

Welcome to the Board!

I take it that you are wanting to capture the sheet names from your original file. The problem is that you are grabbing the sheet name from the new workbook you are adding, not your original file. Since they will all be "Sheet1", you will have issues.

Try this variation, where you capture the sheet name before adding each new workbook:

Sub ExpCsv()

Dim i As Integer
Dim myFileName As String

For i = 1 To 3

Sheets(i).Activate
myFileName = Sheets(i).Name
Range("A1:W645").Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="S:\Com\Monthly\Test\" & myFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Next

End Sub