Cell references change after running macro

[rvelt1213]

I have a spreadsheet that emailed orders are pasted to a specific cell. A macro is then run to populate a grid, basically rearranging the information to then be copy/pasted onto a different spreadsheet. The problem is - after I run the macro, all of the cell references in the grid change to #REF!. I have never had this happen before and cannot figure out how to make the cell references stay put! Any suggestions would be appreciated!

 

[rollis13]

Suggestion: show the macro and the layout of your sheet, elsewise, send us a crystal ball.

 

[rvelt1213]

The references in A2, F2:L2 and M2:N6 are what keep changing. The order information is pasted into A8 and varies in length.

FedEx Web Order input.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Order #		8/25/2022	FedEx		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
3
4
5
6
ARC orders

Cell Formulas
Range	Formula
A2	A2	="Order #"&RIGHT(A8,6)
C2	C2	=TODAY()
F2	F2	=VLOOKUP("Ship Attn",$A$8:$B$29,2,FALSE)
G2	G2	=VLOOKUP("Ship Address",$A$8:$B$29,2,FALSE)
H2	H2	=VLOOKUP("Ship CSZ",$A$8:$C$29,3,FALSE)
I2	I2	=VLOOKUP("Ship CSZ",$A$8:$D$29,4,FALSE)
J2	J2	=VLOOKUP("Ship CSZ",$A$8:$E$29,5,FALSE)
K2	K2	=VLOOKUP("Phon*",$A$8:$B$29,2,FALSE)
L2	L2	=VLOOKUP("Ship To",$A$8:$B$29,2,FALSE)
M2	M2	=IF($B$34="","",$B$34)
N2	N2	=IF($B$33="","",$B$33)
M3	M3	=IF($B$38="","",$B$38)
N3	N3	=IF($B$37="","",$B$37)
M4	M4	=IF($B$42="","",$B$42)
N4	N4	=IF($B$41="","",$B$41)
M5	M5	=IF($B$46="","",$B$46)
N5	N5	=IF($B$45="","",$B$45)
M6	M6	=IF($B$50="","",$B$50)
N6	N6	=IF($B$49="","",$B$49)

And the macros:

Sub TrimSpacesType1()
'
' TrimSpacesType1 Macro
'

'
Range("B8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B46"), Type:=xlFillDefault
Range("B8:B46").Select
ActiveWindow.SmallScroll Down:=-14
Selection.Copy
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B8:B46").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub
Sub TTCType1()
'
' TTCType1 Macro
'

'
Range("A10:A46").Select
Selection.TextToColumns Destination:=Range("A10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("B25").Select
Selection.TextToColumns Destination:=Range("B25"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
End Sub
Sub TrimPart()
'
' TrimPart Macro
'

'
ActiveWindow.SmallScroll Down:=9
Range("B32:B52").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveWindow.SmallScroll Down:=-21
End Sub


Sub Type1()
Call TrimSpacesType1
Call TTCType1
Call TrimPart
End Sub
Sub DeleteOrder()
'
' DeleteOrder Macro
'

'
Range("A8:F107").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-73
End Sub

 

[rollis13]

That happens because you are deleting the cells which are recalled in those formulas. In macro 'TrimSpacesType1' nstead of deleting clear the contents.

'Selection.Delete Shift:=xlUp
Selection.ClearContents

 

[rvelt1213]

That happens because you are deleting the cells which are recalled in those formulas. In macro 'TrimSpacesType1' nstead of deleting clear the contents.

'Selection.Delete Shift:=xlUp
Selection.ClearContents

Brilliant!!!! I had to change my "DeleteOrder" macro to Clear Contents rather than deleting, but didn't see the one in "TrimSpacesType1".

I may have yelled out loud when it worked after updating! It was a very exciting moment!! Thank you so much

 

[rollis13]

In fact, I saw that you had used 'ClearContents' elsewhere and I said to myself "why not here too?"; now I understand .
Thanks for the positive feedback, glad having been of some help.