VBA复制公式到其他工作表，不要移动“查看”

Question

我在用一个VBA例程复制

源表：命名范围（多个“选择”）

下面是VBA代码部分。

            ' 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

Answer 1

解决这个问题的唯一方法是重置活动表：

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

    .. code including copy & PasteSpecial ...

    activeWs.Activate
End Sub