Creating a pivot table using all named ranges
 

I'm trying to write a macro to create a pivot table based on all the named
ranges in the current workbook WITHOUT including the danged print areas.
Each sheet has a range named after that worksheet (Example: the "Sheet1"
worksheet has a named range called "Sheet1", and so forth for all sheets). I
only want to include *those* named ranges and not the "Print_Area" ranges.
The pivot table would then be placed onto a new sheet.

The real problem that I'm having is that the macro needs to work for various
workbooks, each of which contain a different number of sheets. I'm having a
hard time figuring out how to nest the "For Each Sheet ... Next" loop into
the pivot table creation code.

Am I making this harder than it should be (like I did with my previous
question tonight)?

Thanks,

excelguru
(Should I really be calling myself "excelguru" in THIS place? I think not.)

Creating a pivot table using all named ranges
 

Hi Excelguru


Dim sh as worksheet
For each sh in activeworkbook.worksheets
'assuming that your range name is always the sheet-name
Range("sh.name").select 'or whatever you want to do
Next

Does that help?

Wolf


-----Original Message-----
I'm trying to write a macro to create a pivot table
based on all the named
ranges in the current workbook WITHOUT including the
danged print areas.
Each sheet has a range named after that worksheet
(Example: the "Sheet1"
worksheet has a named range called "Sheet1", and so
forth for all sheets). I
only want to include *those* named ranges and not
the "Print_Area" ranges.
The pivot table would then be placed onto a new sheet.

The real problem that I'm having is that the macro needs
to work for various
workbooks, each of which contain a different number of
sheets. I'm having a
hard time figuring out how to nest the "For Each
Sheet ... Next" loop into
the pivot table creation code.

Am I making this harder than it should be (like I did
with my previous
question tonight)?

Thanks,

excelguru
(Should I really be calling myself "excelguru" in THIS
place? I think not.)


.

Creating a pivot table using all named ranges
 

Sorry, mistake
Range(sh.name).select
is correct

-----Original Message-----
Hi Excelguru


Dim sh as worksheet
For each sh in activeworkbook.worksheets
'assuming that your range name is always the sheet-name
Range("sh.name").select 'or whatever you want to do
Next

Does that help?

Wolf


-----Original Message-----
I'm trying to write a macro to create a pivot table
based on all the named
ranges in the current workbook WITHOUT including the
danged print areas.
Each sheet has a range named after that worksheet
(Example: the "Sheet1"
worksheet has a named range called "Sheet1", and so
forth for all sheets). I
only want to include *those* named ranges and not
the "Print_Area" ranges.
The pivot table would then be placed onto a new sheet.

The real problem that I'm having is that the macro
needs
to work for various
workbooks, each of which contain a different number of
sheets. I'm having a
hard time figuring out how to nest the "For Each
Sheet ... Next" loop into
the pivot table creation code.

Am I making this harder than it should be (like I did
with my previous
question tonight)?

Thanks,

excelguru
(Should I really be calling myself "excelguru" in THIS
place? I think not.)


.

.