How to create Pivot from Data when you don't know the number of data rows.

Hi, New here and not very good with Excel Macros. I have a spreadsheet where I am pulling in a file and placing the data in a RAW_Data tab. Then I run another macro that creates the pivot table.
I added the variable code to the beginning of Pivot macro. I think I have this coded correctly but keep getting error "Run-Time error 1404: Reference isn't valid"
Can someone help? Thank you.

Administrator's note: Please take the time to review our rules. There aren't many, and they are all important. Our guidelines recommend code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

What line causes the error?

I suggest you attach your actual file so we can run your code. See yellow banner at the top of the page.

You can select the entire columns for your data, assuming you do not have unrelated data below the relevant data. This way you do not have to keep updating the source range for your pivot table if you add more rows of data.

Please Login or Register  to view this content.

Are you sure Sheet1 is the code name for the RAW_DATA sheet?

Sheet1 is the START_HERE sheet, so this returns 0:

Please Login or Register  to view this content.

and you can't then use column 0 in the source data for the pivot.

Yes, you should. I would suggest changing the routine to something like this:

Please Login or Register  to view this content.