Paste Range of formula Cells not changing

Question

I am trying to paste a range of formula into a specific row.

But the formula cells doesn't change:

=BDH('Hist Dorian'!$B$3;"PX_LAST";A13;A13)

A13 is supposed to change while pasting :

On this screen example the cells formula should be :

=BDH('Hist Dorian'!$B$3;"PX_LAST";A23;A23)
=BDH('Hist Dorian'!$B$3;"PX_LAST";A24;A24)
=BDH('Hist Dorian'!$B$3;"PX_LAST";A25;A25)
=BDH('Hist Dorian'!$B$3;"PX_LAST";A26;A26)
=BDH('Hist Dorian'!$B$3;"PX_LAST";A27;A27)

And so on.. But cells does not change

Below is my code :

Private Sub Workbook_Open()

Dim AJD As Date
Dim LastDate As Date

Application.Calculation = xlManual

AJD = Now

AJD = Day(AJD) & "/" & Month(AJD) & "/" & Year(AJD)

If AJD <> CDate(ThisWorkbook.Worksheets("Histo").Range("A13").Value) Then



    LastDate = CDate(ThisWorkbook.Worksheets("Histo").Range("A13").Value)
    NumberOfIt = DateDiff("D", LastDate, AJD)
    
    MsgBox ("MaJ Historique, Derniere Mise a jour : " & LastDate & " Insertion de " & NumberOfIt & " nouvelles lignes")
    
    DoEvents
    For i = 1 To NumberOfIt - 1
        ThisWorkbook.Worksheets("Histo").Rows(13).Insert shift:=xlShiftDown
        ThisWorkbook.Worksheets("Histo").Range("A13").Value = DateAdd("d", i, LastDate)
         With ThisWorkbook.Worksheets("Histo")
            lrow = NumberOfIt
            .Range("B13:DM13").Formula = .Range("B5:DM5").Formula
        End With
    Next


End If
ThisWorkbook.Worksheets("Primary Pricer").Activate
Application.Calculation = xlAutomatic
MsgBox ("Historique MaJ terminé")
End Sub

EDIT

When I insert a New Row how can I update formula in order to change the cells ?

For example I insert a Row(13) So the formula below should be =BDH('Hist Dorian'!$B$3;"PX_LAST";A14;A14) and not =BDH('Hist Dorian'!$B$3;"PX_LAST";A13;A13) but it does not change as excepted

Edit 2

Sub pasteFormulas()
    Range("F6").Copy
    Range("G6").PasteSpecial Paste:=xlPasteFormulas
    ThisWorkbook.Worksheets("Name").Rows(6).Insert shift:=xlShiftDown
End Sub

rustyBucketBay rustyBucketBay · Accepted Answer · 2020-08-03T10:04:23

Try:

.Range("B13:DM13").Copy 
.Range("B5:DM5").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

in the for loop.

Below the sample code I tried that works for a cell:

Sub pasteFormulas()
    Range("F6").Copy
    Range("G6").PasteSpecial Paste:=xlPasteFormulas
    'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        'SkipBlanks:=False, Transpose:=False
End Sub

Paste Range of formula Cells not changing

1 Answers