Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Variable Array of Text Cells | Excel Discussion (Misc queries) | |||
Reading data from another sheet where data is in variable cells | Excel Discussion (Misc queries) | |||
sumproduct - reading a variable value | Excel Discussion (Misc queries) | |||
Trying to use cells as a variable to calculate(count) an array | Excel Programming | |||
Reading Textfile into a variable | Excel Programming |