Line or lines needed to ignore if sheet not available in a VBA code

[jack109]

Hi

Is there a piece of code that could be added to this, so that if a sheet isn't available ihn te workbook it will just ignore and move onto the save the next CSV file instead of me having a debug issue.

Thanks

Sub CSV_SAVE()

Sheets("t_faves").Select
ChDir "C:\SPREADS\"
ActiveWorkbook.SaveAs Filename:="C:\SPREADS\CSV\t_faves.csv", _
FileFormat:=xlCSV, CreateBackup:=False

Sheets("j_faves").Select
ChDir "C:\SPREADS\"
ActiveWorkbook.SaveAs Filename:="C:\SPREADS\csv\j_faves.csv", _
FileFormat:=xlCSV, CreateBackup:=False

Sheets("BF_FLAT").Select
ChDir "C:\SPREADS\"
ActiveWorkbook.SaveAs Filename:="C:\SPREADS\csv\BF_FLAT.csv", _
FileFormat:=xlCSV, CreateBackup:=False


ChDir "C:\SPREADS\"
    ActiveWorkbook.SaveAs Filename:="C:\SPREADS\SETUP.xlsm", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
       

Sheets("SPREADSHEET COPY").Select
End Sub

 

[Joe4]

I would create a User Defined Function that checks to see if a certain file exists. You can then re-use that to check to see if each sheet exists before launching into the code to create the file.
See: How To Check If A Worksheet Exists Using VBA | How To Excel.

 

[Alex Blakenburg]

Does this help at all ?4

Sub CSV_SAVE()

Dim ws As Worksheet
Dim ShtName As Variant
Dim sPath As String, sPathCsv As String
Dim arrShtNames As Variant

arrShtNames = Array("t_faves", "j_faves", "BF_FLAT")
sPath = "C:\SPREADS\"
sPathCsv = sPath & "csv\"

For Each ShtName In arrShtNames

    If Evaluate("isref('" & ShtName & "'!A1)") Then
        Set ws = Worksheets(ShtName)
        ws.SaveAs Filename:=sPathCsv & ShtName & ".csv", _
            FileFormat:=xlCSV, CreateBackup:=False
    End If

Next ShtName

ThisWorkbook.SaveAs Filename:=sPath & "SETUP.xlsm", FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
       
Sheets("SPREADSHEET COPY").Select
End Sub

 

[jack109]

Is there a way I could amalgamate both solution's?

I've put the code into the sheet so to see if the worksheet exists and that works fine. I was wondering this peice of code can be changed?

arrShtNames = Array("t_faves", "j_faves", "BF_FLAT")
sPath = "C:\SPREADS\"
sPathCsv = sPath & "csv\"

I would like to use the sheet names from the column (F) that is used for =WorksheetExists(F4) code instead. F4 to F40 would contain the list of the sheets I want to save as CSV files. That way when I want a new sheet to be used I can add it to column F instead of manualling typing it into this arrShtNames = Array("t_faves", "j_faves", "BF_FLAT")

Thanks

 

[Alex Blakenburg]

Try:

Sub CSV_SAVE_v02()

Dim ws As Worksheet, wsMain As Worksheet
Dim ShtName As Variant
Dim sPath As String, sPathCsv As String
Dim arrShtNames As Variant
Dim i As Long

Set wsMain = ActiveSheet                ' <--- Ideally use Worksheets("ActualSheetName")

With wsMain
    arrShtNames = .Range("F4", .Cells(Rows.Count, "F").End(xlUp)).Value
End With

sPath = "C:\SPREADS\"
sPathCsv = sPath & "csv\"

For i = 1 To UBound(arrShtNames)
    ShtName = arrShtNames(i, 1)
    If Evaluate("isref('" & ShtName & "'!A1)") Then
        Set ws = Worksheets(ShtName)
        ws.SaveAs Filename:=sPathCsv & ShtName & ".csv", _
            FileFormat:=xlCSV, CreateBackup:=False
    End If

Next i

ThisWorkbook.SaveAs Filename:=sPath & "SETUP.xlsm", FileFormat _
    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
       
Sheets("SPREADSHEET COPY").Select
End Sub

 

[jack109]

getting this message

t_faves.csv cannot be accessed. The file may be corrupted, located on a server thats not responding, or read-only.

if I switch to my original code the CSV's save fine?