Syntax for items inside and outside of a Pivot Table

[jomili]

Trying to automate a process, and getting stuck on defining things. I think I've spelled it out in my workbook, but here's the background:

I start off creating a pivot via a macro. Got that part, no problem. Now I need to build formulas, labels, relative to items in the pivot, and I'm getting stymied by lack of syntax.

• The Row of Headers, the items that go in the pivot ROWS area, what do I call that row in a macro?
• The first cell outside (to the right of) that pivot, on that ROWS row, how do I call that?
• The row above my ROWS row has a column with the name of "Projection". It's the last column of my pivot. So Row1, "Projection" column. How do I pinpoint that cell?
• The cell two columns away from "JOURNAL_ID" but on the same row, or one column away from '(Multiple Items)", how do I deposit a value in that cell?
• The formula in my first column to the right of my pivot has to take the average for all of my "2023_*" fields EXCEPT "2023_01". How do I do that formula?

Sorry to have to come for help, but I'm humble enough to know when my poor brain just doesn't have the firepower I need.

BTW, I would include my macro, but it currently only goes to completing the pivot, and that's where I get stuck. But "PT" means "Pivottable" all through it.

[TMS]

See: https://peltiertech.com/referencing-...ranges-in-vba/

[jomili]

TMS,
Good tutorial, one I've bookmarked long ago. But I'm too dull to make it work for me. For instance, the code below selects the FY_AP part of my ROWS row, so 9 columns. But where I need to get is to the first non-pivot cell to the right of that range. Using "Offset(0, 1)." moves my entire selection to the right one column, but what I want is that specific cell. So instead of the below code,

Please Login or Register  to view this content.

I need something like

Please Login or Register  to view this content.

Still trying to figure out how to do the rest of it as well.

[TMS]

Try Offset(0, 1, 1, 1)

[jomili]

I tried it like this:

Please Login or Register  to view this content.

and got a Run-time error "450", Wrong number of arguments or invalid property assignment. I thought that would happen because everything I've read says the VBA Offset only has two parameters.

[TMS]

Sorry, thinking of the worksheet function (which I rarely use).

Maybe try .Offset(0, 1).Resize(1, 1) then.

[jomili]

Using it like so

Please Login or Register  to view this content.

, the code selects J12, so right line, but wrong cell. Trying to hit Q12. Once I find that I think the headers and formulas can be done by defining that column and using offset.

[jomili]

I can get it like this

Please Login or Register  to view this content.

, but the 8 in the Offset depends on how many months of expense there are.

So going this route, I'd have to define a variable which is the count of "2023*" in the DataRange row, and add 1 to it.

Seems like there should be an easier to way to say "First cell outside of the DataRange"