Open Sheet, Run Macro, Extract data
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
|