Error in Code to Sort, Hide, and PDF Worksheets

Hi,

I'm currently working with the following code to reorder my Excel worksheets based on a list (of certain worksheet names) in a specified range (in this case "WorksheetNames"). Once the worksheets in that range are sorted, the next function hides the remaining worksheets based on a similar list (in this case, the range "Hidden"). Finally, the code should PDF the newly reordered worksheets only.

QUESTION: This code works (sorts the worksheets, and hides the others, but doesn't yet PDF), but is currently returning a "Select method of Worksheet class failed" error (I believe error 400?), but I'm not quite sure what the issue could be. I'm fairly new to VBA, and have pieced the code together from various sources, so any help in getting it to work properly would be greatly appreciated. Thanks!

Sub test()
    Dim rng As Range, i As Long, msg As String
    On Error GoTo Errorcatch
    Set rng = Range("WorksheetNames")
    For i = rng.Count To 1 Step -1
        If rng(i).Value <> "" Then
            If IsSheetExists(rng(i).Value) Then
                Sheets(rng(i).Value).Move after:=rng.Parent
            Else
                msg = msg & vbLf & rng(i).Value
            End If
        End If
    Next
    rng.Parent.Select
    If Len(msg) Then MsgBox "Wrong sheet name" & msg
    Set rng = Nothing
    Exit Sub
Errorcatch:
    MsgBox Err.Description
End Sub
 
 
Function IsSheetExists(txt As String) As Boolean
    On Error Resume Next
    For Each cell In Sheets("Appendices").Range("Hidden")
        ' Hide sheets
        Sheets(cell.Value).Visible = False
    Next cell
End Function

Function SaveFileToPDF()
    ActiveWorkbook.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:="C:\Compustat Data\Test.pdf", _
            OpenAfterPublish:=True
End Function

mbhart,

Welcome to the forum. Please take a moment to read the forum rules: http://www.excelforum.com/forum-rule...rum-rules.html

Particularly rule #3 which pertains to code tags.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

[code]
your code here ...
and here ...
and here
[/code]

... and appear like this when posted:

your code here ...
and here ...
and here

e/ I didn't error in that location.

On what line does debug highlight for you?

Please attach your workbook as an example showing the order you want the sheets sorted, and the sheets that are supposed to be hidden.

To attach a workbook, click the 'Go Advanced' button below and use the paperclip in the toolbar to navigate to your document.

Are all the sheets supposed to be "unhidden" afterwards?

Dim l As Long
Dim was As Worksheets
Dim arrSheets() As Integer
Dim i As Integer: i = 1

With Sheets("Appendices")
    For l = 6 To .Range("K" & .Rows.Count).End(xlUp).Row
        If .Cells(l, "K").Text <> "" Then
            Sheets(.Cells(l, "K").Value).Move after:=Sheets(i)
            ReDim Preserve arrSheets(i)
            arrSheets(i) = Sheets(.Cells(l, "K").Value).Index
            i = i + 1
        End If
    Next l
    For l = .Range("N" & .Rows.Count).End(xlUp).Row To 6 Step -1
        If .Cells(l, "N").Text <> "" Then
            Sheets(.Cells(l, "N").Value).Visible = False
        End If
    Next l
    .Activate
End With

arrSheets(o) = 1
Sheets(arrSheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Compustat Data\Test1.pdf", OpenAfterPublish:=True
    
End Sub

If the sheets only have to be hidden on the pdf and not the excel file this you can do it with less loops:

Sub mbhart2()
Dim l As Long
Dim was As Worksheets
Dim arrSheets() As Integer
Dim i As Integer: i = 1

With Sheets("Appendices")
    For l = 6 To .Range("K" & .Rows.Count).End(xlUp).Row
        If .Cells(l, "K").Text <> "" Then
            Sheets(.Cells(l, "K").Value).Move after:=Sheets(i)
            ReDim Preserve arrSheets(i)
            arrSheets(i) = Sheets(.Cells(l, "K").Value).Index
            i = i + 1
        End If
    Next l
    .Activate
End With

arrSheets(o) = 1
Sheets(arrSheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Compustat Data\Test1.pdf", OpenAfterPublish:=True
    
End Sub

Because you're missing sheet9. (I had to add it into your example workbook). I edited the code in post number 10. Try it after adding in sheet9.

Glad to help. And welcome to the forum.

If you are happy with the solution please mark the thread as [SOVLED] using thread tools at the top.

And on any thread you can show your appreciation to those who have helped you by clicking the * below their post.