VBA copy formula to other sheet, do not move “view”

Question

I'm using a VBA routine to copy

Source sheet: named range (multiple 'selections')

Target sheet: copy-paste formula from cells in named range, to cells with a certain offset from a cell on the target sheet (this offset is depending on a certain selection).

I have two types of named ranges on the source sheet; one from which I only want to copy the values (=rng_operationeel_input_data), one from which I would like to copy to formulas (=rng_operationeel_formules). The formulas should be copied 'relatively', to have references on the target sheet (which are part of the copied values from the other range). For that reason, I can't use "targetCell.Formula = sourceCell.Formula", as it then literally copies the absolute formula. Not relative. That's why I'm using sourceCell.

I do this in a for each loop over all cells in the source range, as the named range is not one single range (set of ranges).

Note. 'datasetReferenceCell' is the cell on the target sheet from which the offset is taken for pasting.

The problem is that, even if I use VBA to do the copy-pasting, without using 'select' somewhere, still at the end the user is confronted with the target sheet. (Excel will move to the target sheet) This is only happening, for the copy-paste part.

How can I prevent this from happening?

edit: note that I am already using "Application.ScreenUpdating" (at start to false, at the end to true). I also have a MsgBox at the end of the routine (for info that routine was successful). Excel is moving to the target worksheet after the MsgBox is closed.

Below the VBA code part.

            ' dataset for weeknr found: save data to dataset
        Dim dataRange As Range, dataField As Range

        ' for each cell in input data range: save value in dataset
        Set dataRange = Range("rng_operationeel_input_data")
        For Each dataField In dataRange
            datasetReferenceCell.Offset(dataField.Row, dataField.Column).Value = dataField.Value
        Next dataField

        ' !!! Following are only saved, not loaded, as it are formula based fields

        ' for each cell in formula range: paste formula
        Set dataRange = Range("rng_operationeel_formules")

        For Each dataField In dataRange
            dataField.Copy
            datasetReferenceCell.Offset(dataField.Row, dataField.Column).PasteSpecial (xlPasteFormulas)
            'datasetReferenceCell.Offset(dataField.Row, dataField.Column).Formula = dataField.Formula 'not working, as relative formulas are required
        Next dataField

not sure if I understand, but you can copy directly without select Range("rng_operationeel_input_data").Copy Destination:=Range("rng_operationeel_formules") msdn.microsoft.com/VBA/Excel-VBA/articles/… — Slai
I only want to copy the formula (hence 'xlPasteSpecial'), but in such a way that the cells are copied relatively. If I use your method, I also get all other stuff (like markup, security etc.). If I use "targetCell.Formula = sourceCell.Formula", I get the formula with absolute cell reference. — FBE

FBE FBE · Accepted Answer · 2017-06-23T10:18:12

The only way I was able to solve it was to reset the active sheet:

Public Sub routine()
    Dim activeWs As Worksheet
    Set activeWs = ThisWorkbook.ActiveSheet

    .. code including copy & PasteSpecial ...

    activeWs.Activate
End Sub

VBA copy formula to other sheet, do not move “view”

1 Answers