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.)
.
|