Get Cell Value From Sheet In Sheet Array

[goss]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

The error I receive

Run-time error 9 - Subscript out of range

What else can I check?

thanks
w

[Norie]

What exactly is in the array avSheetsExport()?

Also, how are you looping?

[goss]

THanks Norie,

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

The full code is below
Thx
w

Please Login or Register  to view this content.

[Norie]

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

Please Login or Register  to view this content.

it will be a 2-dimensional array.

That's why you get the subscript error.

For the code you posted this should work.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

So to loop this array,

Please Login or Register  to view this content.

it should be this,

Please Login or Register  to view this content.

or, perhaps better.

Please Login or Register  to view this content.

[goss]

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[goss]

File
Thx
w

[Norie]

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,

Please Login or Register  to view this content.

should be this.

Please Login or Register  to view this content.

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?

[goss]

Thanks Norie,

I test the values in each array

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[goss]

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:

Please Login or Register  to view this content.

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

Thx
w

[JosephP]

your sheet names are numeric-use

Please Login or Register  to view this content.

to force them to be treated as strings

[goss]

Thanks JP,

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

thx
w

[JosephP]

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

Please Login or Register  to view this content.

you must use

Please Login or Register  to view this content.

or declare your array as a string to begin with

[Norie]

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

[goss]

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

[Norie]

I think this does what you describe.

[goss]

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

[Norie]

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:.

[JosephP]

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

I would need a revised workbook to see why

[goss]

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

[JosephP]

this line

Please Login or Register  to view this content.

oughta be

Please Login or Register  to view this content.

[Norie]

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.

[goss]

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

[Norie]

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.

Please Login or Register  to view this content.

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.

[goss]

Thanks Norie,

Works perfectly!

Thx
w