autofill named range after row insert

[mynameisnfs]

Hi:

I'm trying to figure out how to autofill the formulas after inserting a row. I figured out how to copy and insert, however, this keeps the formula the same, instead of the autofill increment.

This code does the insert/copy with named range "Last_Year":

Please Login or Register  to view this content.

I tried removing the .copy and adding a .offset.filldown/fillup but the results were not autofilling.

I tried adding this after the end With, and although it kind of works, it prompts an object defined error message:

Please Login or Register  to view this content.

What I'm trying to do:
Convert sales data report from 12 month year to 13 periods. Jan-Dec is somewhere in column A, arbitrary row position. "Last_Year" named range refers to only this monthly column range (essentially the "label" for this "table" of data), whatever row it begins (example: A41:A52). Data for each month extends out through each respective row, and this data is formula driven. Manually click-dragging the forumlas completes autofill correctly by incrementing the columns and row numbers.

After the autofill is done (or before, whatever works better no big deal), I will then iterate through the named range and replace the jan-dec values with P1-P13.

In the code above, I arbitrarily chose row 3 for the insert.. it doesn't really matter I think where. I have several of these named ranges.

I hope the above was clear explanation. Thank you for your time.

[EdRooney]

Hi there
I am not sure I quote follow what you are actually doing, posting an example, with dummy data where required, often helps.
The first thing that comes to mind though, have you tried using tables for your data or named range?
Hope it helps

[mynameisnfs]

Hi there:

Manually derived example of what I'm trying to do. The named range is A35:A46 in the jan-dec image. My simple code above works fine in the data worksheet, as there are no formulas and the insert preserves the formatting. What you see here is a snip of a report template.
From this:
calendar_year.PNG


To this:
p13_year.PNG

I had briefly looked at tables in the past, but they were too challenging to go backwards into the existing templates. The way it works: code iterates through columns of the data worksheet, then makes a copy of the template, and replaces $B in Data!$B## with the respective column.

What is really weird I went ahead and prepared a working example.. and oddly enough it is doing what I thought it would?!?! I am so confused! Hahaha, sorry to waste your time? I've attached the xlsm, I haven't made a button for the function, it's the "convert" sub.

I have started research on how to change the range labels from Jan-Dec to P1-P13 and back, I thought it'd be as simple as copying array over, but it's not

Example:
p13Array = array("P1", "P2"..."P13")
Range("Y_Last").RefersTo = p13Array

[mynameisnfs]

Woohoo, figured out the array to range:

p13Year = Array("P1", "P2", "P3", "P4", "p5", "p6", "p7", "p8", "p9", "p10", "p11", "p12", "p13")
m12Year = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

If converting to 13 period, then:
dataLabel = Application.Transpose(p13Year)

If converting from 13 to 12, then:
dataLabel = Application.Transpose(m12Year)

That application transpose was a booger. Pfffft. Searching how, everyone seems to want to do range to array, had to do it the other way around.

[mynameisnfs]

Here it is for anyone interested.. no clue if the way I'm doing this is efficient, but it seems to work.