Cycle through worksheets vba

**twhitezell** · 04-02-2010, 12:48 PM

I'm working on a book, and I need to add a pivot chart to each worksheet in my workbook. I have everything set up right now to do what I need on one of the pages, but i'm clueless on where even to begin trying to get it to cycle through each worksheet, excluding the first 4 worksheets. I'm sure there is some kind of easy fix, but like i said, i've got no clue. This is what I have so far:

Please Login or Register  to view this content.

**davegugg** · 04-02-2010, 12:54 PM

You can cycle through like so:

Please Login or Register  to view this content.

Fill in the worksheets you want to skip (your first four) in the second line.

Does that work for you?

**twhitezell** · 04-02-2010, 01:04 PM

that gets the cycling to go, now in the code:

Please Login or Register  to view this content.

how can I set the source date just to the active sheet and not to a static sheet?

**davegugg** · 04-02-2010, 01:06 PM

Anywhere it references a sheet that you want it active instead of static, change it to ActiveSheet. instead of Sheets("...").

That should do it.

**twhitezell** · 04-02-2010, 01:15 PM

The only problem i get now is just with setting the pc variable

Please Login or Register  to view this content.

where it gets the source data, it has a static reference, but I can't figure out how to work in the activesheet

**twhitezell** · 04-02-2010, 01:59 PM

Actually, the more I look at it, the more problems that seem to come up.

Please Login or Register  to view this content.

Here is selects the data for the Pivot table, but i need to somehow get it to select row1column1:R.end(xlup)C8. you know what i'm saying? there are always going to be 8 columns, but the number of rows will vary by sheet (which i still can't figure out how to set it to change in the above code). but anyway, anything helps

**davegugg** · 04-05-2010, 10:47 AM

Unfortunately, I'm not very experienced working with PivotTables in VBA.
Does the set pc... line give you an error? If so, what is the error message? Is the SourceData:= argument changing based on what sheet you are on?

**twhitezell** · 04-05-2010, 10:52 AM

right now it works for the sheet ics-accountmanagement... but when it goes to the next sheet i get an error that says "run time error 1004" A PivotTable report with that name already exists on the destination sheet"

so it's trying to put it back on the same sheet, when i need it to go on the current active sheet

**twhitezell** · 04-05-2010, 10:59 AM

ok, actually, i've got part of it fixed

Please Login or Register  to view this content.

now I just have to figure out how to make the source data so that it will change for each worksheet, because now when it runs, i get the same graph on every page, but is there a way to define a range for the active worksheet that always selects a range that would be row2column 1 to row X column 8? where x is the last row of data?