Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.) . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.) . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem creating named ranges in a Macro! | Excel Discussion (Misc queries) | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
creating a pivot table w/ ranges from 2 worksheets | Excel Discussion (Misc queries) | |||
Creating dymnamic named ranges | Excel Programming | |||
Creating Named Ranges in VBA | Excel Programming |