OUTSTANDING, Tom.........thanks ever so much and also for the extra hint
about the brackets......I'll change it all over tomorrow........this new
modification you've made for me ought to do the whole thing up fine. Now I
can get the data I need from each sheet, regardless of where it's located on
the sheet.

Many thanks again,
Vaya con Dios,
Chuck, CABGx3


"Tom Ogilvy" wrote in message
...
If you want to move the assignment statements inside each of the case
statements, I guess that would work.

also, you don't need to use brackets around your function names and I
would
recommend against it since brackets are usually used as a shortcut for
evaluate (may add some overhead, although I haven't tested it).

Sub GetDataAll()
Dim pIndex As Long
Dim pSheet As Excel.Worksheet
For pIndex = 1 To 2 '38
Select Case pIndex
Case 1
Set pSheet = ActiveWorkbook.Worksheets("3MX1")
[GoTo3MX1]
[Update3MX1]
ActiveWorkbook.Worksheets("ReportSheet").Cells(pIn dex, 1) = _
Range("FirstSheetValueOne)
ActiveWorkbook.Worksheets("ReportSheet").Cells(pIn dex, 2) = _
Range("FirstSheetValueTwo")
Case 2
Set pSheet = ActiveWorkbook.Worksheets("3MX2")
[GoTo3MX2]
[Update3MX2]
ActiveWorkbook.Worksheets("ReportSheet").Cells(pIn dex, 1) = _
Range("SecondSheetValueOne)
ActiveWorkbook.Worksheets("ReportSheet").Cells(pIn dex, 2) = _
Range("SecondSheetValueTwo")
End Select
Next
Worksheets("ReportSheet").Select
End Sub


--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Hi Tom.........
The code is in a General Module.
Here it is for two sheets........

Option Explicit
Sub GetDataAll()
Dim pIndex As Long
Dim pSheet As Excel.Worksheet
For pIndex = 1 To 2 '38
Select Case pIndex
Case 1
Set pSheet = ActiveWorkbook.Worksheets("3MX1")
[GoTo3MX1]
[Update3MX1]
Case 2
Set pSheet = ActiveWorkbook.Worksheets("3MX2")
[GoTo3MX2]
[Update3MX2]
End Select
ActiveWorkbook.Worksheets("ReportSheet").Cells(pIn dex, 1) =
pSheet.Cells(2,
2)
ActiveWorkbook.Worksheets("ReportSheet").Cells(pIn dex, 2) =
pSheet.Cells(6,
3)
Next
Worksheets("ReportSheet").Select
End Sub

It works beautifully using the cell addresses for the values I want to
retrieve, but I was wondering if instead I could give each of those
cells
it's own RangeName, (like "FirstSheetValueOne", and
"FirstSheetValueTwo",
etc), and use those RangeNames in the code instead of the cell
addresses............possible?

Value con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote in message
...
if it is a workbook level name, then you can use something like

If the code is in a general module
Range("Name1").Value = 123

If the code is in a sheet module, you would have to preface the range
by
the sheet on which the named range is located.
Worksheets("ReportSheet).Range("Name1").Value = 123
It is unclear to me how you would use named ranges in your macro, so I
can't
recommend anything specifically.

--
Regards,
Tom Ogilvy




"CLR" wrote in message
...
How cool it is!!!!!.........you guys are amazing...........Thank you
Jezebel
and Thank you Tom, really a lot!.......I got it working on two
sheets
and
I'm sure the rest will go well............incidently, I use XL97 and
had
to
change the "ActiveBook" terms to "ActiveWorkBook"..........the macro
kept
hiccuping on them.

One other question if you will, can I go after individual cells with
RangeNames instead of specific cell addresses?

Again, thank you both, most kindly

Vaya con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote in message
...
It doesn't have to be worked in. He put in the lines:

ActiveBook.Worksheets("ReportSheet").Cells(pIndex, 1) =
pSheet.Cells(2,2)
ActiveBook.Worksheets("ReportSheet").Cells(pIndex, 2) =
pSheet.Cells(6,3)

in the proper place in the loop.

You just need to add the other 36 case statements and all 38 macro
calls.
.

--
Regards,
Tom Ogilvy



"CLR" wrote in message
...
Thanks Jezebel............I see you included a piece about
sending
the
data
I wished to capture to the ReportSheet.........but it's not
clear
to
me
where I should meld that part in.........I guess I did not make
it
clear
in
my first post, the situation is such that when I open a sheet
and
run
the
macro, it sets up the data on the sheet with certain values,
those
two
B2
and C6 I wish to capture AT THAT POINT and send to the
ReportSheet,
right
then because as I switch off each sheet another macro fires
which
will
cause
the values in those two cells to change, and THOSE values, I do
not
want..........a little more clarification as to how to work this

in
to
the
code would be appreciated..........

Vaya con Dios,
Chuck, CABGx3




"Jezebel" wrote in message
...
Dim pIndex as long
Dim pSheet as Excel.Worksheet

For pIndex = 1 to 38

Select case pIndex
Case 1
set pSheet = ActiveBook.Worksheets("sheetname 1")
[macro for sheet 1]

Case 2
...

end select

ActiveBook.Worksheets("ReportSheet").Cells(pIndex, 1) =
pSheet.Cells(2,2)
ActiveBook.Worksheets("ReportSheet").Cells(pIndex, 2) =
pSheet.Cells(6,3)

Next





"CLR" wrote in message
...
Hi All.............

If someone would be so kind, I have great need. I have a
XL97
workbook
with
38 hidden sheets. I would like to open each sheet by name,
one
by
one,
run a
macro unique to that sheet, (avg time = 2 minutes), and then
extract
the
values in cells B2 and C6 (after the macro has run as the
data
will
change)
to a "ReportSheet" in columns A and B, then on to the next
sheet,
etc
etc.
Any assistance or direction would be greatly appreciated.

TIA
Vaya con Dios,
Chuck, CABGx3