Open Sheet, Run Macro, Extract data

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>