Problem with Excel's SELECT or WORKSHEET. No idea why

Question

In the code below I've created an array with a set of worksheets (by name) that I will then process to a PDF file. I've done similar code before without an issue.

However, this time the "SELECT" coding is creating "Error 1004: Application-defined or object-defined error". Ive tried using Worksheets().Select and wkBkObj.Worksheets().Select to replace the Sheets().Select in the code below. In each case the same error.

Any help would be welcome! The section of code is below:

        Dim arrSplit, arrTemp, vbResult, arrWorkSheetNames() As Variant
        Set wbThisWorkBook = ThisWorkbook '<--- wbThisWorkBook is passed in as a parameter
        wbThisWorkBook.Activate

        iCount = wbThisWorkBook.Worksheets.Count

        ReDim arrWorkSheetNames(1 To iCount)
        For i = 1 To iCount
            bOKToAdd = True
            sTemp3 = UCase(wbThisWorkBook.Worksheets(i).Name)

            For j = LBound(arrStringsToMatch) To UBound(arrStringsToMatch)
                sTemp4 = UCase(CStr(arrStringsToMatch(j)))
                iTemp = CInt(StrComp(UCase(sTemp3), UCase(sTemp4), vbTextCompare))
                If (iTemp = 0) Then
                    bOKToAdd = False
                    Exit For
                End If
            Next
            If bOKToAdd Then
                iNumElements = iNumElements + 1
                arrWorkSheetNames(iNumElements) = ActiveWorkbook.Worksheets(i).Name 'Add to array
            End If
        Next
        If (iNumElements > 0) Then
            ReDim Preserve arrWorkSheetNames(1 To iNumElements)

            Sheets(arrWorkSheetNames).Select 'This is where Error: 1004 occurs!

            With Sheets(arrWorkSheetNames(1))
                If ((.Visible <> xlSheetHidden) And (.Visible <> xlSheetVeryHidden)) Then
                    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileSaveAsFullName, _
                     Quality:=xlQualityStandard, OpenAfterPublish:=True
                End If
            End With
        Else
            '<do something else>
        End If

You are trying to select an array of sheets it appears. You need to put the array index in there to tell it which element of the array you want. You can probably just skip all the [activating and selecting](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) though. — Warcupine, Apr 06 '20 at 17:43
@Rick check the values in your array. Every one of them must be a name of an existing sheet in the Workbook — chris neilsen, Apr 06 '20 at 19:33
@Rick that said, your code Selects multiple sheets, then exports only one of them. Why? What are you trying to achieve? — chris neilsen, Apr 06 '20 at 19:36
Chris: I have indeed stepped through the code to ensure that the information in the array is indeed the appropriate worksheet names. Warcupine: I'm selecting a subset from all the worksheets in the workbook, then trying to use the created array to pass through the Excel internal PDF printing option. Also, from your comment regarding indexing the array, are you referring to something as straightforward as using a For...Next loop and referencing each item in the array of worksheet names? Similar to: For k = 1 to UBound(arrWorkSheetNames) : Sheets(arrWorkSheetNames(i)).Select ...Next — Rick, Apr 06 '20 at 20:57
@Warcupine: I've tried this code. Problem is that once the 1st sheet is processed an error occurs on the next pass. The PDF doesn't remain open for writing the after 1 sheet. I'm looking for a single PDF containing all the sheets. ''' For k = 1 To iNumElements With Worksheets(arrWorkSheetNames(k)) .ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=sFileSaveAsFullName, _ Quality:=xlQualityStandard, OpenAfterPublish:=True End With Next k ''' — Rick, Apr 06 '20 at 21:21
@Warcupine: Here's the code I found online that I've based my original coding on. Perhaps the example doesn't actually work? Sheets(Array("Sheet1", "Sheet2")).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:="C:\Users\marks\Documents\Saved PDF.pdf" This code implies that all of the sheets in the array will end up in a single resultant PDF, which is what I'm trying to achieve. In the example the directly listed array entries are instead represented by the array I've created with the sheet names within. Sorry that I can't get code lines to appear properly. — Rick, Apr 06 '20 at 21:32
@Warcupine: For the life of me I don't see why this change made a difference, however, I moved the test for ".Visible" [ie.: If ((.Visible <> xlHidden)...] to earlier--when deciding which sheets are to be included in the array--and now the code using the .Select works as it does in my other project. I don't understand why the addition of the If statement within the "With...End With" matters, but apparently it does... Thanks for the suggestions and for offering to assist. Both are greatly appreciated. — Rick, Apr 06 '20 at 21:59
@chrisneilsen: Read last comment added for Warcupine. Especially the last: Thanks for the suggestions, and for offering to assist. Both are appreciated! — Rick, Apr 06 '20 at 22:01
@Rick I think it was just looking at whatever sheet was in (1) in the array inside the with block. Not too sure though honestly. — Warcupine, Apr 07 '20 at 13:26

Problem with Excel's SELECT or WORKSHEET. No idea why

0 Answers0