Find Sheet Names, then use correct SaveAs Macro

[SuperFerret]

Hi All,

I am trying to amend some code prevent me needing 2 seperate macro's that do almost the same thing.

I have workbooks which I have to save 3 worksheets, SU, MER and PF and I have to save these as .txt in a specific Directory. However, there are some workbooks which don't require/contain the SU and MER sheets.

I would like to be able to have one button (in my QAT) that can evaluate whether all 3 sheets are there, or just the PF sheet and save accordindgly. There will always be other sheets in these workbooks that do not require saving (only use for calculations)

I snatched the bones of this code from another thread, but I can't get it to work:

Sub FetaCheeseElves()
Dim i As Long
 
Application.DisplayAlerts = False
For i = 1 To Worksheets.Count
 
Select Case Sheets(i).Name
     Case "PF"
        ChDir "C:\TRANSIT"
    Sheets("PF").Select
    Application.SaveAs Filename:="C:\TRANSIT\TRANSIT.txt", FileFormat:= _
        xlText, CreateBackup:=False
     Case Else
        ChDir "C:\TRANSIT"
    Sheets("PF").Select
    Application.SaveAs Filename:="C:\TRANSIT\TRANSIT.txt", FileFormat:= _
        xlText, CreateBackup:=False
    Sheets("MER").Select
    Application.SaveAs Filename:="C:\TRANSIT\SEEDS.txt", FileFormat:= _
        xlText, CreateBackup:=False
    Sheets("SU").Select
    Application.SaveAs Filename:="C:\TRANSIT\LEVELS.txt", FileFormat:= _
        xlText, CreateBackup:=False
    End Select
Next i
Application.DisplayAlerts = True
 
End Sub

If anyone can point me in the right direction or offer any advice, I'd appreciate it.

 

[Sektor]

Found some errors in function and sub. Now all works. Tested.

Function SheetExists(SheetName As String) As Boolean
    Dim sh As Worksheet
    On Error Resume Next
    Set sh = Worksheets(SheetName)
    If Not sh Is Nothing Then SheetExists = True
End Function

Sub FetaCheeseElves()

    Dim i As Long, arr As Variant, arr2 As Variant
     
    Application.DisplayAlerts = False
    
    arr = Array("SU", "MER", "PF")
    arr2 = Array("LEVELS", "SEEDS", "TRANSIT")
    
    For i = LBound(arr) To UBound(arr)
        If SheetExists(CStr(arr(i))) Then
            Worksheets(arr(i)).SaveAs filename:="C:\" & arr2(i) & ".txt", _
                                  FileFormat:=xlTextWindows, _
                                  CreateBackup:=False
        End If
    Next

    Application.DisplayAlerts = True
     
End Sub

 

[SuperFerret]

Sektor you are fantastic! Thank you!

 

[Sektor]

Not at all!