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

I assume by macro unique to that sheet, you mean you have 38 separate
macros. Then the problem is just to loop through a list of sheet names and
use a corresponding list of macro names to identify the unique macros.


Dim v, v1, rw as Long, i as Long
Dim sh as Worksheet
v = Array("Sheet1", _
"Sheet5", _
. . .
"Sheet2)
v1 = Array("Macro1", _
"Macro5", _
. . .
"Macro2")

rw = 1
for i =lbound(v) to ubound(v)
set sh = worksheets(v(i))
sh.Activate
Application.Run v1(i)
rw = rw + 1
with worksheets("ReportSheet")
.Cells(rw,1),.Value = sh.Range("B2").Value
.Cells(rw,2).Value = sh.Range("C6").Value
End with
Next

--
Regards,
Tom Ogilvy



"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

That looks like it's just the ticket Tom.............thanks ever so much.
Time for me to leave now, I'll work on it over the weekend...........

Thanks again, I do appreciate your help!
Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

I assume by macro unique to that sheet, you mean you have 38 separate
macros. Then the problem is just to loop through a list of sheet names and
use a corresponding list of macro names to identify the unique macros.


Dim v, v1, rw as Long, i as Long
Dim sh as Worksheet
v = Array("Sheet1", _
"Sheet5", _
. . .
"Sheet2)
v1 = Array("Macro1", _
"Macro5", _
. . .
"Macro2")

rw = 1
for i =lbound(v) to ubound(v)
set sh = worksheets(v(i))
sh.Activate
Application.Run v1(i)
rw = rw + 1
with worksheets("ReportSheet")
.Cells(rw,1),.Value = sh.Range("B2").Value
.Cells(rw,2).Value = sh.Range("C6").Value
End with
Next

--
Regards,
Tom Ogilvy



"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

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

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

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

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

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

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

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

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