changing range selection for pivot table based on worksheet

[twhitezell]

I've got some code that i need to go through every different worksheet in my workbook and create a pivot table. I recorded the macro to make the pivot table, but i don't know how to change the source data to be flexible. basically i need it to always be from row 2 column 1 to row (last row) column 8, so only the second row will change. also, I don't know how to change the worksheet source, so that it is always the active worksheet.

here is what i have so far:

Please Login or Register  to view this content.

my main issues are coming at the lines:

Please Login or Register  to view this content.

and you can see that the sheet for source data is set, as well as the sheet for the pivot table (which i also don't know how to change to the active sheet)

any thing will help, thanks

[JeanRage]

Hi,

A simple solution for you is to name your ranges and use Dynamic ranges ... i.e ranges which adapt themeselves to the database ...

Should you need some explanation ...
http://www.contextures.com/xlNames01.html#Dynamic

HTH

[twhitezell]

that sounds good, my only problem, is that the names and number of worksheets that I will be cycling through will vary from time to time, is there a way to make the dynamic range one that will automatically change for different worksheets rather than defining one range for each of the sheets?

[JeanRage]

Hi,

If you have a great deal of worksheets and database ranges, a macro can automatically generate all the Range Names ...

HTH

[twhitezell]

It ended up being even easier than that, i just defined a variable as the last row, and set the source data equal to

Please Login or Register  to view this content.

and the lastRow variable changes with each worksheet

Thanks for your help