Pivot Table VBA Changing Data Range

Good day,

I'm working on a macro that will be taking data from some tables and putting it into a table, and then generating a pivot table from that data. Where i am having trouble is getting the pivot cache to accept my references. I have one main problem that I think is giving me a problem.

It is grabbing the data from specific sheets and areas correctly, but the pivot cache is having an issue. I think that the issue here is that I need the range of the data to be flexible. It is defined by two variables (Top1 and Bottom1) to distinguish the x range for this particular set of data. I am having a hard time getting the cache to accept my grammar.

I am not sure now to get it to be variable using the R1C1 formatting, but I am having a tough time getting it to accept it in absolute terms.

Please Login or Register  to view this content.

Dark Green is setting the ranges in another sheet to cut the right info and place it into my "Pivot Data" sheet
Red is just pasting and formatting said data into the sheet I want to use to feed my pivot table
black is the portion of the code that is defining the range of the data to be dragged into the pivot. I BELIEVE i need to come up with a way to incorporate this into the rest of my code, but that is only the best way I have come to do this kind of thing so far--recommendations are appreciated
Green is my two thoughts as to how to do this but i keep getting a plethora of errors no matter what i try.

Thanks all,
~Joe

Try something like this:

Please Login or Register  to view this content.

Is the data in column I formulas or values?

By the way, there is no need to keep quoting my entire post back at me - it just makes the thread harder to read.

Can you post a workbook? I can't see any reason why that wouldn't work as it is.

Bit of a longshot but in the code you definitely have

Please Login or Register  to view this content.

with a period in front of the word Range and not just:

Please Login or Register  to view this content.

Sorry - I really should have spotted that!

The With...End with holds a reference to an object so you don't have to refer to it explicitly when using several properties/methods of the same object. The period means that what follows belongs to the object specified in the With part.

The column I range references were being related back to the column Z reference, not the worksheet, so actually referred to a range 9 columns to the right of Z. That's what you fixed and I should have spotted!

Yeah - especially if you're hacking stuff around in a hurry without testing.