Add column using VBA & references in formula shift - simple solution?

[vinvinvin123]

Hello!

Once a month I receive a report that is not in the correct format. This report is the source data for another sheet I use to check the information the report contains against data from a few other sources. My aim was to save the new report in the same location each time, then use a button / macro on my analysis sheet to open the report, format it correctly, save it - the info pulled into my analysis sheet would then be up to date.

My VBA code to format the report is as follows:

Sub delplanformatting1()
'
' delplanformatting1 Macro
' add column, convert to number, convert to del date
'
Workbooks.Open "Delivery Plan.xlsm"
Range("A1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select
Selection.Copy
Range("C2", "C8000").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K2", "K8000").Select
Selection.NumberFormat = "m/d/yyyy"

Range("K2").Select

Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""",0,IF(RC[-1]=""no date"",0,IF(LEN(RC[-1])=20,DATE(VALUE(MID(RC[-1],14,4)),1,1)+((RIGHT(RC[-1],2))*7),IF(RC[-1]="" Stock"",TODAY(),DATE(VALUE(MID(RC[-1],FIND("" "",RC[-1])+1,4)),1,1)+((RIGHT(RC[-1],2))*7)))))"
Range("K2").Select
Selection.Copy
Range("K3", "K8000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
'

End Sub

This works well & formats the sheet correctly however the formula I have on my analysis sheet also changes as a result. The formula is as follows & giving the correct result:

=SUMPRODUCT(--('[Delivery Plan.xlsm]Delivery Plan'!$C$2:$C$8000=$B6),
--('[Delivery Plan.xlsm]Delivery Plan'!$K$2:$K$8000>=DATE(2018,MONTH(U$5&"/1"),1)),
--('[Delivery Plan.xlsm]Delivery Plan'!$K$2:$K$8000<=DATE(2018,MONTH(U$5&"/1")+1,1)-1),'[Delivery Plan.xlsm]Delivery Plan'!$M$2:$M$8000)

This formula works correctly with a formatted report. I then overwrite with the new report & try to use the macro to reformat & get the formula changes to the following:

=SUMPRODUCT(--('Delivery Plan'!$C$2:$C$8000=$B6),
--(Delivery Plan'!$L$2:$L$8000>=DATE(2018,MONTH(U$5&"/1"),1)),
--(Delivery Plan'!$L$2:$L$8000<=DATE(2018,MONTH(U$5&"/1")+1,1)-1),
Delivery Plan'!$N$2:$N$8000)

Each column in the formula has shifted so the formula is no longer correct. Is there a simple solution to this problem?

I thought of making the additional column the last column in the report so it doesn't shift position of any others but was wondering if there was a better / technically correct way of doing this?

Sorry for the long winded post, hope someone can help!

 

[vinvinvin123]

Hello, sorry to bump my own thread but I was just wondering if anyone had any suggestions?

I was surprised since I had used $ to lock the formula that the column references still changed after adding a column to the other sheet. Is there a way to fix the formula so it doesn't change?

Thanks.