Search code examples
excelvbaexcel-formulaautocomplete

VBA Excel, formula, stop Excel changing the formula in the table

I have a button that creates a table, (report generator) in this table I link to various pages with the same syntax.

Worksheets("Engine").Range("E" & EngineStatus).Formula = "=" & newSheetName & "!I3"

It is working, and it is running on a loop. However, when I am linking the value from each sheet, in the same cell on each sheet. But of course only for selected sheets.

The problem occurs on the report, as seen on the screenshot.

The sheet name in this case is ACDTCM0137 and cell I3 is what I put in my code.

The output is it counts I as an increased number which it should not. And it overwrites ALL rows in this column with its LAST value.

So the last sheet might be called BDMETHR0148 and same cell. But the last one in, is the one it shows for ALL rows.

enter image description here

How do I ensure that for each row it keeps the formatting from the cove above?

Meaning it should always be by this syntax <sheetname!i3>

Solution

  • You should read about relative and absolute reference in Excel.

    Instead:

    Worksheets("Engine").Range("E" & EngineStatus).Formula = _
                "=" & newSheetName & "!I3"

    Use:

    Worksheets("Engine").Range("E" & EngineStatus).Formula = _
                "=" & newSheetName & "!$I$3"    ' dollar sign $ before column and row locks it

    You wrote that you have button to create report, so maybe the code below will be even better (not recalculating until report generated again - values only, not formula):

    ' all vars before loop "dimmed" only once
 (...)
 Dim rngEngine As Range  
 Dim rngStatus As Range

 ' And in your loop 
 EngineStatus = ...
 newSheetName = ...

 Set rngEngine = Worksheets("Engine").Range("E" & EngineStatus)
 Set rngStatus = Worksheets(newSheetName).Range("I3")

 rngEngine.Value = rngStatus.Value