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: copy-data

Categories: vba

Updated: September 25, 2018

Twitter Facebook LinkedIn

Leave a Comment