Pivot table macro, Dynamic range

[V3rse]

I'm pretty new to VBA and I'm working on a macro that will create a pivot table based on a sheet that will have additional data (rows) every few days. Therefore I need the range the pivot is pulling from to be dynamic. Selecting the entire sheet that the data is coming from is also an option, but I couldn't successfully find a way to do that.

I've been fiddling with Dim's without much success( I don't think I understand their syntax very well) to try to make the rows variable.

Any suggestions are appreciated. Thanks for your help.

[HaHoBe]

Hi, V3rse,

Excel will record the area in R1C1-Notation but you could try to substitute the code by

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Please be informed that if there are blanks in the names used it should read "'Data Fresh'" instead of "Data Fresh".

Ciao,
Holger

[V3rse]

That notation does seem more user friendly, but how can I use that to make the range dynamic? I tried your code and it only wants to select cell A1. I tried changing the range values to "a1:z50000" and it did the same thing.

50000 would be more than enough to cover the cells I need after the data it's pulling from is complete, but some of the cells will likely be blank during that time. Will that keep the pivot table from working correctly?

note: there was also an extra period which I removed

[HaHoBe]

Hi, V3rse,

how can I use that to make the range dynamic?

Already done with CurrentRegion as that marks the continuous area around Range("A1") no matter how many rows there will be in the sheet. Continuous means no empty rows or columns which will end the block of data.

Maybe use one of these alternatives as well: name the range "Database" and rely on that as range-object, make your data a table, get the last row of data and use that in the code:

Please Login or Register  to view this content.

Sorry about the extra period.

Ciao,
Holger

[jashiag]

Please Login or Register  to view this content.

[arlu1201]

jashiag,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.