Reading variable list of cells into array
 

This should be a pretty easy question, but I just can't seem to be
able to figure it out. I've not done much VBA programming with
arrays.

I have in cells A4 through A49 a list of job numbers. Each job number
has not only a row of data on this sheet, but another sheet whose name
is the job number. For exampl, cell A4 contains 99909, and there is
also a sheet named 99909.

The list of jobs varies with each new set of data. For example, next
month, there may be 40 rows of data, and 99909 may not be on the list.

I want to read the sheet names into an array and perform an action
(printing) on the array, something like this:

For each cell in A4 through end of list, add cell value to array
When end of list is reach, print each sheet in array

Thanks in advance.

Reading variable list of cells into array
 

I have a question. You asked about printing the list in A4 thru A49 (the
ending row being variable month to month), but what about the data on that
row... do you want them moved to the worksheets having the same names as the
contents of A4:A49 or do you already have that part coded?

--
Rick (MVP - Excel)


"Diffus" wrote in message
...
This should be a pretty easy question, but I just can't seem to be
able to figure it out. I've not done much VBA programming with
arrays.

I have in cells A4 through A49 a list of job numbers. Each job number
has not only a row of data on this sheet, but another sheet whose name
is the job number. For exampl, cell A4 contains 99909, and there is
also a sheet named 99909.

The list of jobs varies with each new set of data. For example, next
month, there may be 40 rows of data, and 99909 may not be on the list.

I want to read the sheet names into an array and perform an action
(printing) on the array, something like this:

For each cell in A4 through end of list, add cell value to array
When end of list is reach, print each sheet in array

Thanks in advance.

Reading variable list of cells into array
 

Hi,

Don't think you need to use the array:

Sub PrintSheets()
Dim cell As Range
Dim mySheet As String
Range([A4], [A4].End(xlDown)).Select
For Each cell In Selection
mySheet = cell
Sheets(mySheet).Activate
ActiveWindow.SelectedSheets.PrintOut
Next cell
End Sub

--
Thanks,
Shane Devenshire


"Diffus" wrote:

This should be a pretty easy question, but I just can't seem to be
able to figure it out. I've not done much VBA programming with
arrays.

I have in cells A4 through A49 a list of job numbers. Each job number
has not only a row of data on this sheet, but another sheet whose name
is the job number. For exampl, cell A4 contains 99909, and there is
also a sheet named 99909.

The list of jobs varies with each new set of data. For example, next
month, there may be 40 rows of data, and 99909 may not be on the list.

I want to read the sheet names into an array and perform an action
(printing) on the array, something like this:

For each cell in A4 through end of list, add cell value to array
When end of list is reach, print each sheet in array

Thanks in advance.

Reading variable list of cells into array
 

Sorry, I didn't include this in my question, but I want all sheets to
print at one time (to a PDF file). If I select each sheet and print
it individually, I'm going to have 30+ files, when I want one. I
figured reading the sheet names into an array would be the best way to
accomplish that, so that's why I phrased the question in those terms.
I guess I'm back to the original question.

On Sep 28, 11:14*am, ShaneDevenshire
wrote:
Hi,

Don't think you need to use the array:

Sub PrintSheets()
* * Dim cell As Range
* * Dim mySheet As String
* * Range([A4], [A4].End(xlDown)).Select
* * For Each cell In Selection
* * * * mySheet = cell
* * * * Sheets(mySheet).Activate
* * * * ActiveWindow.SelectedSheets.PrintOut
* * Next cell
End Sub

--
Thanks,
Shane Devenshire

Reading variable list of cells into array
 

Rick,

The data on the rows are a summary of data on the sheets. The
formulae on the row are array formulae, while the formulae on the
sheets are sums of array formulae thereon. I compare the two to make
sure they're the same before sending the printed sheets to to clients.

On Sep 28, 10:40*am, "Rick Rothstein"
wrote:
I have a question. You asked about printing the list in A4 thru A49 (the
ending row being variable month to month), but what about the data on that
row... do you want them moved to the worksheets having the same names as the
contents of A4:A49 or do you already have that part coded?

--
Rick (MVP - Excel)

Reading variable list of cells into array
 

Does anyone have any suggestions for other message boards or websites
where I might be able to find an answer to this question?

TIA.