Get Cell Value From Sheet In Sheet Array

Hi all,

Using Excel 2013 64-bit
As I am looping and loading a sheet array I could like to get a cell value from one of the sheets to use as the filename when I export to pdf

I tried

Sheets(avSheetsExport(1)).Select
sFileName = ActiveSheet.Range("A1")

I confirmed the LBound and UBound of the array are 1 and 3 respectively

Debug.Print LBound(avSheetsExport)
Debug.Print UBound(avSheetsExport)

The error I receive

Run-time error 9 - Subscript out of range

What else can I check?

thanks
w

What exactly is in the array avSheetsExport()?

Also, how are you looping?

THanks Norie,

avSheetsExport is a sheets array that is loaded a filtered range specialcells(xlvisible)

The full code is below
Thx
w

Option Explicit
Option Base 1
Sub ExportToPDF3()

    'Excel evironment
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
    
    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim wsControl As Worksheet
        Dim wsWork As Worksheet
        Dim wsWork2 As Worksheet
        Dim lTabColor As Long
        Dim i As Long, j As Long, k As Long, l As Long
        Dim lRows As Long, lWorkRows As Long, lControlRows As Long
        Dim rColorIndex As Range, rWork As Range, rCriteria As Range
        Dim rControlTabName As Range, rControlColorIndex As Range
        Dim avColorIndex() As Variant
        Dim avCriteria() As Variant
        Dim avSheetsExport() As Variant
        Dim avSheets() As Variant
        Dim sFilePrefix As String
        Dim sFileSuffix As String
        Dim sPath As String, sFileName As String, sExtension As String
    
    'Object reference
        Set wb = ThisWorkbook
        Set wsControl = wb.Worksheets("Control")
        Set wsWork = wb.Worksheets("Work")  'Color index
        Set wsWork2 = wb.Worksheets("Work2")
        Set rWork = wsWork.Range("A1")
    
    'Intialize
        i = 2
        avSheets = Array("Control", "Work", "Work2")
        sFilePrefix = InputBox("What is the file prefix?")
        sFileSuffix = InputBox("What is the file suffix?")
        sPath = "C:\Data\"
        sExtension = ".pdf"
        
        
    'Make sure autofilter is off
    'Make sure all rows are visible
    'Clear previous data from sheets that hold temporary data
        For l = 1 To UBound(avSheets)
            With wb
                Set ws = .Worksheets(avSheets(l))
                With ws
                    If .FilterMode Then
                        .ShowAllData
                    End If
                    .UsedRange.ClearContents
                End With
            End With
        Next l
    
    'List sheets and tab color index
        With wb
            For Each ws In .Worksheets
                lTabColor = ws.Tab.ColorIndex
                Select Case lTabColor
                    Case -4142
                        'Tab has no color
                        'Do nothing
                    Case Else
                        With wsControl
                            .Cells(i, 1).Value = ws.Name
                            .Cells(i, 2).Value = lTabColor
                            i = i + 1
                        End With
                End Select
            Next ws
        End With
        
    'Header Row
        With wsControl
            .[A1].Formula = "TabName"
            .[B1].Formula = "TabColorIndex"
        End With
        
    'Generate unique list from color index list
        With wsControl
            lRows = .Cells(Rows.Count, 2).End(xlUp).Row
            Set rColorIndex = .Range("B1:B" & lRows)
            
            rColorIndex.AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=rWork, _
            Unique:=True

        End With
        
    'Load the color index data into an array
        With wsWork
            lWorkRows = .Cells(Rows.Count, 1).End(xlUp).Row
            ReDim avColorIndex(1 To lWorkRows)
            For j = 1 To UBound(avColorIndex)
                avColorIndex(j) = .Cells(j, 1)
            Next j
        End With
          
    'Loop through the color index array
            For j = 2 To UBound(avColorIndex)
            
            'Filter the color index range
                avCriteria = Array(avColorIndex(1), avColorIndex(j))
                Set rCriteria = wsWork.Range("IV1:IV2")
                rCriteria = WorksheetFunction.Transpose(avCriteria)
            
            'Range to filter
                With wsControl
                    lControlRows = .Cells(Rows.Count, 1).End(xlUp).Row
                    Set rControlColorIndex = Range(.Cells(1, 1), .Cells(lControlRows, 2))
                End With
                        
            'Filter the data
                rControlColorIndex.AdvancedFilter Action:=xlFilterInPlace, _
                CriteriaRange:=rCriteria, _
                Unique:=False
                rCriteria.ClearContents
                
            'Load the visible range into a sheet array
                With wsControl
                    Set rControlTabName = Range(.Cells(2, 1), .Cells(lControlRows, 1))
                    Debug.Print rControlTabName.Address
                    Set rControlTabName = rControlTabName.Rows.SpecialCells(xlCellTypeVisible)
                    Debug.Print rControlTabName.Address
                    ReDim avSheetsExport(1 To rControlTabName.Rows.Count)
                    Debug.Print rControlTabName.Rows.Count
                    avSheetsExport = rControlTabName
                    
                        Debug.Print LBound(avSheetsExport)
                        Debug.Print UBound(avSheetsExport)
                    If .FilterMode = True Then
                        .ShowAllData
                    End If
                End With
                
                
            'Get the filename from the first sheet in the sheet array
                Sheets(avSheetsExport(1)).Select
                sFileName = ActiveSheet.Range("A1")
                
            'Export the sheet array to pdf file
                Sheets(avSheetsExport()).Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=sPath & sFilePrefix & "_" & sFileName & "_" & sFileSuffix & sExtension, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
 
                
        Next j

    
    'Tidy up
        'Erase arrays
            Erase avColorIndex
            Erase avSheets
            Erase avSheetsExport
            
        'Destroy objects
            Set rControlColorIndex = Nothing
            Set rControlTabName = Nothing
            Set wsWork = Nothing
            Set wsWork2 = Nothing
            Set wsControl = Nothing
            Set wb = Nothing
            
        'Restore Excel environment
            With Application
                .ScreenUpdating = True
                .DisplayAlerts = True
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
            End With
End Sub

Since you use this to populate avSheetsExport from the range rControlTabName,

 avSheetsExport = rControlTabName

it will be a 2-dimensional array.

That's why you get the subscript error.

For the code you posted this should work.

sFileName = Sheets(avSheetsExport(1)).Range("A1").Value

I wasn't able to check that though as I can't get the code to actually run that far

If you want/need a 1-dimensional array you'll need to redimension it.

By the way, this isn't needed and is actually a bit confusing - I thought it was the cause of the problem.

  ReDim avSheetsExport(1 To rControlTabName.Rows.Count)

Oh, and when you use Array to create an array it's index starts at 0.

So to loop this array,

    avSheets = Array("Control", "Work", "Work2")

it should be this,

For I = 0 to UBound(avSheets)

or, perhaps better.

For I = LBound(avSheets) To UBound(avSheets)

File
Thx
w

Thanks Norie.

I'm using Option Base 1.

I changed from transfering the range to the array to looping the range to load the array:

ReDim avSheetsExport(1 To rControlTabName.Rows.Count)
                    For Each C In rControlTabName
                        avSheetsExport(k) = C.Value
                        k = k + 1
                    Next C

I used your code to try to get the cell value from the 1st sheet of the sheetsarray, but I am getting a cell value from a different sheet array

There are 2 sheet arrays:
avSheets and avSheetsExport

The code is correct, using the avSheetsExport array

                sFileName = Sheets(avSheetsExport(1)).Range("A1").Value

Yet, the value being returned is from the other sheets array.

Any thoughts on why that may be?
The file is attached in case that is helpful.

thx
w

Even if you are using Option Base 1 it's a good idea to use LBound and UBound instead of hard-coding the values for the loop.

I only referred to avSheetsExport in the first part of the post so I don't see how you would get values from another array using what I suggested.

I did make a typo though, this,

sFileName = Sheets(avSheetsExport(1)).Range("A1").Value

should be this.

sFileName = Sheets(avSheetsExport(1,1)).Range("A1").Value

That's irrelevant though since you've changed the code.

Have you checked what values avSheetsExport is being populated with and which sheet they come from?

Thanks Norie,

I test the values in each array

For k = LBound(avSheetsExport) To UBound(avSheetsExport)
                    Debug.Print avSheetsExport(k)
                Next k
                
                For k = LBound(avSheets) To UBound(avSheets)
                    Debug.Print avSheets(k)
                Next k

The output is as expected

1.1
1.2
1.3
Control
Work
Work2

I am trying to get the value from worksheet 1.1 which should be "1"
Instead I am getting the value from worksheet Control which is "TabName"

It seems regarless of the code, the value is being picked up from worksheet control as the activesheet
I tried

                Debug.Print ActiveSheet.Name

Sure enough, Control is the activesheet, not 1.1 as I expect

It seems then that this is not activating the first sheet in the sheets array

Sheets(avSheetsExport(1)).Activate
                sFileName = Sheets(avSheetsExport(1)).Range("A1").Value

Bump.

Does anyone have any thoughts as to why the value being returned is from the wrong sheets array?

In addition to everyting above, I tried:

            'Get the filename from the first sheet in the sheet array
'                Sheets(avSheetsExport).Select
'                Sheets(avSheetsExport(LBound(avSheetsExport))).Select
                Sheets(avSheetsExport(LBound(avSheetsExport))).Range("A1").Activate
                Sheets(avSheetsExport(LBound(avSheetsExport))).Select
                Debug.Print "Sheet 1 Export Array: " & (avSheetsExport(LBound(avSheetsExport)))
                Debug.Print "activesheet: " & ActiveSheet.Name
                sFileName = Sheets(LBound(avSheetsExport)).Range("A1").Value

Still, the value for sFileName is coming from avSheets instead of avSheetsExport.

Thx
w

your sheet names are numeric-use

                sFileName = Sheets("" & avSheetsExport(1)).Range("A1").Value

to force them to be treated as strings

Thanks JP,

I changed to your code.
The active sheet still remains with the incorrect sheets array

thx
w

the principle applies to the rest of the code too-wherever you use

Sheets(avSheetsExport(1))

you must use

Sheets("" & avSheetsExport(1))

or declare your array as a string to begin with

What exactly, in words, are you trying to do with this code?

Thanks Norie

Objectives
[1] Loop through workbook of 124 + tabs
[2] Get Color index of tabs (Currenly 14 unique - may grow in the future)
[3] Develop uniique list of color index
[4] Create sheets array of sheets with same color index
[5] Export the sheets array to pdf file
[6] Loop for next colorindex

thx
w

I think this does what you describe.

JP,

I conterted to string and confirmed with application.worksheetfunction.istext(asSheetsExport(i))
All came back TRUE
Still, the active worksheet is avSheets(1) which is "Control"

Norie,

Thanks.
Your code output 2 .pdf files and then returned an error

Run-time error '1004':
Document not saved. The document may be open, or an error may have been encountered when saving

However, one of the files that was exported to pdf was the Control tab which is incorrect. Only, the colored tabs should be exported to pdf

thx
w

Originally Posted by goss

Still, the active worksheet is avSheets(1) which is "Control"

I would need a revised workbook to see why

Norie,

I'm looking at your file.
I'm unclear how it could hve output to C:\Data\ when the path is C:\Test\?

JP,

New File is attached after converting the second sheets array from variant to string

thx
w

There is no Control worksheet in the workbook I uploaded.

The worksheets that are used in the code are created at runtime, and should be deleted afterwards - forgot to add code for that.

Did you make any changes to the workbook or code I posted?

PS I assume you changed the file path, I only changed it for testing purposes since I don't have a Data folder on C:.

this line

sFileName = Sheets(LBound(asSheetsExport)).Range("A1").Value

oughta be

sFileName = Sheets(asSheetsExport(LBound(asSheetsExport))).Range("A1").Value

My code couldn't have output anything to C:\Data.

Are you sure there weren't existing files in that folder?

Can I ask, did you try the code in the workbook I attached with that workbook without changing either workbook or code?

It does work, see the 2 pdf files I uploaded here https://www.box.com/s/vw1k52xahshtlpk9w7rx and here https://www.box.com/s/qp3uximsmzp3ao4uqw6m.

JP,

Still does not work.

Probably have to give up at this point and work on Norie's code.

Norie,

It's close, take a look at the output you posted, both files show 1.1 in cell A1
The file named 1.1 should show 1.1
The file named 2.1 should show 2.1

When I ran it, I received files 1.1 and 2.2 respectively.

Thanks
w

The problem is I didn't deselect the previous sheets.

That's why the 2nd pdf file ends up with all the sheets.

Should be an easy fix.

This will make sure that only the sheets in avSheetsExport as selected.

        ' new line

        Sheets(CStr(avSheetsExport(LBound(avSheetsExport)))).Select
        
        For i = LBound(avSheetsExport) To UBound(avSheetsExport)
            Sheets(CStr(avSheetsExport(i))).Select False
        Next i

I've updated the file, added the above and deleted the temporary sheets - see the attached.

PS Remember and change sPath to the appropriate directory.

Thanks Norie,

Works perfectly!

Thx
w