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]

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

Sub FetaCheeseElves()

    Dim i As Long, arr As Variant[B][B], arr2 As Variant[/B][/B]
     
    Application.DisplayAlerts = False
    
    arr = Array("SU", "MER", "PF")
    [B][COLOR="Red"]arr2 = Array("LEVELS", "SEEDS", "TRANSIT")[/COLOR][/B]
    
    For i = LBound(arr) To UBound(arr)
        If SheetExists(CStr(arr(i))) Then
            Sheets(arr(i)).SaveAs filename:="C:\TRANSIT\" & [B][COLOR="red"]arr2[/COLOR][/B](i) & ".txt", _
                                  FileFormat:=xlText, _
                                  CreateBackup:=False
        End If
    Next

    Application.DisplayAlerts = True
     
End Sub

 

[SuperFerret]

Thanks for the quick response Sektor.

How would I amend this to different Save As Values?

Basically:
PF has to be TRANSIT.txt
MER has to be SEEDS.txt
SU has to be LEVELS.txt

 

[Sektor]

See changes in red in my previous post.

 

[SuperFerret]

Many thanks Sektor...but I can't get it to work?

I've saved it in the Personal.xlsb (as I need to run it from there) in a module. Am I putting it in the right place?

When I step through it never actually hits the Sheets(arr(i)).SaveAs Filename:="C:\TRANSIT\" & arr2(i) & ".txt", FileFormat:=xlText, CreateBackup:=False
section... and I don't know why?

My sheets are named SU MER and PF?

 

[Sektor]

What does error say?

 

[SuperFerret]

It doesn't give me any errors, I run it and nothing happens. It's only when I step through it gets to the If SheetExists(CStr(arr(i))) Then line... goes through the Function:
Function SheetExists(SheetName As String) As Boolean
On Error Resume Next
Set sh = Worksheets(sh)
If Not sh Is Nothing Then
End Function

That's all that highlights in the Function as I step through... Then it highlights End If and carries on going through the other sheets??

 

[Sektor]

You execute code step-by-step? If nothing happens, then there are no such sheets.

 

[SuperFerret]

But I even tried this in a brand new Workbook and created just the 3 sheets in it called SU, MER and PF and some duff data and it didn't work?

Edit: I tried the function in a new workbook to get it to evaluate TRUE and I couldn't make it do it? Even in a brand new workbook with only the sheet "Sheet1" ?

 

[SuperFerret]

Ok, I managed to get past the unrecognised Sheets bit by amending the Function slightly:

Function sheetexists(SheetName As String) As Boolean
    Dim sh As Worksheet
    On Error Resume Next
    Set sh = Worksheets([COLOR=red]SheetName[/COLOR])
    If Not sh Is Nothing Then sheetexists = True
End Function

But now it gives me the old "Run-time error '1004: Application-defined or object-defined error"

here:

Sheets(arr(i)).SaveAs Filename:="C:\TRANSIT\" & arr2(i) & ".txt", _
                                  FileFormat:=xlText, _
                                  CreateBackup:=False

Gutted! Why would it stop there?