Copying Data Worksheet To Worksheet

less than 1 minute read

This will copy formulas from one place to another and then Copy & Paste the calculated results as Special Values There are 2 components, one is the long subroutine which does the grunt work of copying and pasting. The 1 line caller is to be in a separate routine, put here the source sheet and range and the destination sheet and range.

'==================================================================================================
' ## Copy as Special Values Between Worksheets: this is the caller line
'==================================================================================================
Call CopyAndPasteFormulaAsSpecialValues(Sheets("Sheet1"), "A1:B1", Sheets("Sheet1"), "A2:B10")

'==================================================================================================
'	CopyAndPasteFormulaAsSpecialValues, Copy Formulas from one place to another and then
'	Copy & Paste the calculated results as Special Values
'==================================================================================================
Public Sub CopyAndPasteFormulaAsSpecialValues(ByVal SourceWorksheet As Worksheet, _
                                              ByVal FormulaRangeToCopy As String, _
                                              ByVal DestinationWorksheet As Worksheet, _
                                              ByVal RangeToPasteOver As String)

    ' // ensure some error handling to restore events
    On Error GoTo Catch

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' // Copy the initial Formula from one place to another
    SourceWorksheet.Range(FormulaRangeToCopy).Copy _
            Destination:=DestinationWorksheet.Range(RangeToPasteOver)

    ' // Copy & Paste Special Values over the calculated Formula Range
    DestinationWorksheet.Range(RangeToPasteOver).Copy
    DestinationWorksheet.Range(RangeToPasteOver).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

Catch:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Tags:

Categories:

Updated: