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