Export and save IF a specific sheet exists

[srosk]

Here's my code:

Function Export()
Dim Fname As String, ws As Worksheet
Fname = "Vesting Review - " & Sheets("Instructions").Range("D5").Value
Sheets(Array("VDR", "SP", "DDR", "VOR", "TDR")).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws
With ActiveWorkbook
    .SaveAs ThisWorkbook.Path & "\" & (Fname)
    .Close
End With
End Function

It works as expected. However, all sheets will not always exist. Sometimes individual sheets do not exist. How can I account for that?

Can this somehow include an IF(ISREF...) copy sheet1, sheet2, etc.? Not sure best way to accomplish. Ty in advance.

 

[yky]

Can't see the purpose of the copy command.

 

[srosk]

Hmm ok.

 

[Fluff]

How about

Sub MyExport()
   Dim Fname As String, ws As Worksheet
   Dim Ary As Variant
   Dim i As Long
   Fname = "Vesting Review - " & Sheets("Instructions").Range("D5").Value
   Ary = Array("VDR", "SP", "DDR", "VOR", "TDR")
   For i = 0 To UBound(Ary)
      If Not Evaluate("isref('" & Ary(i) & "'!A1)") Then
         MsgBox "sheet " & Ary(i) & " does not exist"
         Exit Sub
      End If
   Next i
   Sheets(Ary).Copy
   For Each ws In ActiveWorkbook.Worksheets
       With ws.UsedRange
           .Value = .Value
       End With
   Next ws
   With ActiveWorkbook
       .SaveAs ThisWorkbook.Path & "\" & (Fname)
       .Close
   End With
End Sub

 

[srosk]

Thanks Fluff! Is there any way for it to say if sheet X does not exist, continue on to sheet Y, etc., instead of ending the function? Appreciate your help!

 

[Fluff]

How about

Sub MyExport()
   Dim Fname As String, ws As Worksheet
   Dim Ary As Variant, tmp As Variant
   Dim i As Long
   Fname = "Vesting Review - " & Sheets("Instructions").Range("D5").Value
   Ary = Array("VDR", "SP", "DDR", "VOR", "TDR")
   tmp = Ary
   For i = 0 To UBound(Ary)
      If Not Evaluate("isref('" & Ary(i) & "'!A1)") Then
         tmp = Filter(tmp, Ary(i), False, vbTextCompare)
      End If
   Next i
   Sheets(tmp).Copy
   For Each ws In ActiveWorkbook.Worksheets
       With ws.UsedRange
           .Value = .Value
       End With
   Next ws
   With ActiveWorkbook
       .SaveAs ThisWorkbook.Path & "\" & (Fname)
       .Close
   End With
End Sub

 

[srosk]

Thank you very much!! That works

 

[Fluff]

Glad to help & thanks for the feedback