0

I have a macro in a workbook that saves selected sheets in a workbook as separate csv files. I thought this was working fine but I now seem to be getting the error message below, I'm not sure if I've inadvertently changed something, as I was trying to write a separate macro to run this one from another workbook. Error message:

Run time error '1004' Cannot access read-only document ' Activity.csv'

Public Sub SaveWorksheetsAsCsv3()

'save workbook before continuing
ActiveWorkbook.Save
Application.DisplayAlerts = False

'save each sheet as csv
 Dim WB As Workbook
 Dim WS As Worksheet
 Dim FolderPath As String
 Dim FileName As String

'folder path for saving
 FolderPath = Range("B3").Value
 
'loop through all sheets
 For Each WS In Worksheets
 
'ignore these sheets
 If WS.Name <> "Control" And WS.Name <> "Summary" Then
    
'copy current sheet
     WS.Copy
     Set WB = ActiveWorkbook
     
'save as csv file
     FileName = WS.Name
     WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
     Application.DisplayAlerts = True
     WB.Close
     Application.DisplayAlerts = False
    End If

 Next WS

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
Chris
  • 1
  • 1
  • Change everything `Active`, e.g. `ActiveWorkbook` with `This`, e.g. - `ThisWorkbook`. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/35864330#35864330 – Vityata Jun 20 '22 at 13:59
  • `Range("B3").Value` really needs a workbook and worksheet qualifier. – Tim Williams Jun 20 '22 at 17:00
  • Thank you both. I think the main issue was the range not having a defined sheet as I realised I was testing the macro from a different sheet. I'm still learning VBA, what difference does 'Active' to 'This' make. – Chris Jun 21 '22 at 07:55

0 Answers0