Creating a pivot table using all named ranges

  • Thread starter Thread starter excelguru
  • Start date Start date
E

excelguru

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

needs
to work for various
.
Click to expand...
 
Back
Top