0

I'm trying to setup a macro in excel to save two sheets from a work book as an csv file.

I want the file name in the format - WorkBook name - Worksheet name

But I can't even get it to save with the work sheet name yet, I get

Can't access read-only document "filename.csv"

The code is:

Sub SaveAsCSV()
savePath = "/Users/****/Library/CloudStorage/Dropbox/Assets/Create Import File/"

For Each xWs In ThisWorkbook.Sheets
    Select Case xWs.Name
        Case "List"
            ActiveWorkbook.SaveAs FileName:=savePath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV
        Case "Evolutions"
            ActiveWorkbook.SaveAs FileName:=savePath & "\" & xWs.Name & ".csv", FileFormat:=xlCSV
        Case Else
    End Select
Next
End Sub

Ceri Turner
  • 830
  • 2
  • 12
  • 36

1 Answers1

0

Tested on Windows.

Option Explicit

Sub SaveAsCSV()

    Dim savePath As String, s As String
    Dim xWs As Worksheet
    
    savePath = "c:\temp\SO\" '"/Users/****/Library/CloudStorage/Dropbox/Assets/Create Import File/"
    savePath = savePath & Replace(ThisWorkbook.Name, ".xlsm", " - ")
    
    For Each xWs In ThisWorkbook.Sheets
        Select Case xWs.Name
            
            Case "List", "Evolutions"
                s = savePath & xWs.Name & ".csv"
                'Debug.Print s
                xWs.Copy
                With ActiveWorkbook
                   .SaveAs Filename:=s, FileFormat:=xlCSV
                   .Close False
                End With

        End Select
    Next
End Sub
CDP1802
  • 13,871
  • 2
  • 7
  • 17