XLOOKUP not working correctly in macro

[Kekda]

Hello!
I'm trying to create a table of data on several products which I will be updating with a new column to the right on a monthly basis. See link: sample.xlsx

To make this easier I've tried to record a simple macro which pulls data from another sheet and puts the data in position for me to review. The steps go as follows:

1. Right click table, enter new column to right
2. In the new 'F' column I have created I enter my xlookup formula which pulls data across nicely, for month September
3. Change rounding up/down as necessary
Then I stop recording and after deleting the data just entered, I run the macro and it works nicely, entering everything I just recorded. However when I run it again straightaway, it creates a new column but the xlookup formula is still stuck in the 'F' column, and so it just recopies the data for September on top. What I'm after is an xlookup formula that shifts over to the new column I've created so I have static data for each month lined up in columns.

Hope this makes sense.
Thanks in advance

 

[Kekda]

Thank you, that works perfectly.

I also have a sparkline which I would like to update as per table not absolute columns. Any way to make this work?

 

[Joe4]

I also have a sparkline which I would like to update as per table not absolute columns. Any way to make this work?

That is an entirely different question that needs its own thread (besides which, I have NEVER worked with Sparklines, so I know absolutely nothing about them).