VBA: How to programmatically insert a row and copy a formula down without breaking references?
After trying multiple solutions online, I still can't figure this out. I'm working on a project and hit a roadblock. I'm sure I'm missing something obvious here, but I'm trying to insert a new row in an Excel worksheet using VBA, and I want to copy a formula from the row above into the newly created row. However, when I do this, the formula sometimes breaks its references, leading to unexpected results. I'm working with Excel 365 and my formula references a specific range which includes both relative and absolute references. Here’s the code I’m using: ```vba Sub InsertRowAndCopyFormula() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Dim lastRow As Long lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Insert a new row at the bottom ws.Rows(lastRow + 1).Insert Shift:=xlDown ' Copy the formula from the row above ws.Cells(lastRow, "B").Copy ws.Cells(lastRow + 1, "B").PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End Sub ``` I’ve tried using `PasteSpecial` with `xlPasteFormulas`, but in certain cases, the formula does not maintain the desired references. For example, if my formula in B10 is `=A10 + $C$1`, after copying it to B11, it sometimes becomes `=A11 + C1`, losing the absolute reference to `$C$1`. I have also experimented with using `ActiveSheet.Formulas` but ran into similar issues. Is there a reliable way to ensure that the references remain intact when copying formulas to a newly inserted row? Any suggestions or best practices would be greatly appreciated! I'm working on a service that needs to handle this. What's the best practice here? My development environment is Ubuntu. Is there a better approach? This issue appeared after updating to Vba 3.11. Am I approaching this the right way?