Help with VBA code - More to paste & pressing enter after a paste is complete

[KiloHotel]

Hi guys,

I am running this code;

Sub
Dim lngMax As Long
Dim lngcounter As Long
Dim lngCalc As Long

lngCalc = Application.Calculation
Application.Calculation = xlCalculationManual
With ActiveSheet
  lngMax = WorksheetFunction.Max(.Range("B" & .Rows.Count).End(xlUp).Row, .Range("C" & .Rows.Count).End(xlUp).Row)
  For lngcounter = 5 To lngMax
    If UCase(.Cells(lngcounter, "B").Value) = "HERE" Then
      .Rows(4).Copy .Rows(lngcounter)
    End If
  Next lngcounter
End With
Application.Calculation = lngCalc
End Sub

My issue is when it pastes the row, in column F of that pasted row I have a formula also pasted however, When the line pastes it does paste in the I have to click each pasted column F cell and just press enter for excel to act on the formula input.

I am sure it is very simple but I cannot wrap my head around it, I have tried adding .Select in and also Range("F6").Select

 

[Micron]

Maybe try a forced recalculation. Not an Excel vba expert but I suspect when you turn calc off then edit then turn back on, it needs another edit or similar event in order to calculate. So before end sub, maybe put
Activesheet.Calculate

 

[Peter_SSs]

Could it be that at some point your code errored before it got to the line near the end that resets the calculation to (presumably) Automatic?
If so, that would leave the calculation mode as Manual.
Next time the code runs it would be

• recorded as Manual by lngCalc = Application.Calculation
• set to Manual by Application.Calculation = xlCalculationManual
• and reset to Manual at the end by Application.Calculation = lngCalc

That is, always Manual

 

[Micron]

Good point. Another case for why I tend to always use an error handler to reset application settings in case of errors.