Pass Range Cells for Copy-Paste: Excel VBA

Question

I have to pass 2 cells (reference or value) from one sub to another sub so that the value in the first cell is copied and pasted to the other cell. Since it is required for multiple cell combinations, I want to get it done by passing the cell (reference or value) from one subroutine to other.

I've tried the following code and combination with Byref and ByVal options. But unable to get the desired results. Essentially, in the below code, whatever value is present in cell E1792 should be copy-pasted to C1799 as Value and Number formatted.

Private Sub Copy_Val(ByRef CopyFrom As Range, ByRef PasteTo As Range)
Application.ScreenUpdating = False

    Range(CopyFrom).Select
    Selection.Copy
    Range(PasteTo).Select

    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Sub Apply_Filter()
    Dim CpFrm As Range
    Dim PstTo As Range
    Set CpFrm = Range("E1792")
    Set PstTo = Range("C1799")
    Call Copy_Val(CpFrm, PstTo)
End Sub

Sometimes I get Type Mismatch error, and sometimes "400" error. Expected output is that the cell value present in cell E1792 should be copy-pasted to C1799 as Value and Number formatted.

Any help in fixing this will be appreciated.

Remember, you always need to specify the workbook and worksheet for each range object — Tim Stack
– Tim Stack, Commented May 28, 2019 at 12:18

Greg Viers · Accepted Answer · 2019-05-28 12:19:05Z

3

Since CopyFrom and PasteTo are Range objects, you don't need to use Range(). You can also eliminate the Select part and simplify your function considerably:

Private Sub Copy_Val(ByRef CopyFrom As Range, ByRef PasteTo As Range)
Application.ScreenUpdating = False

    CopyFrom.Copy
    PasteTo.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

End Sub

answered May 28, 2019 at 12:19

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Error 1004 · Accepted Answer · 2019-05-28 12:17:54Z

1

You could try:

Option Explicit

Public Sub Copy_Val(ByVal CopyFrom As Range, ByVal PasteTo As Range)

    Application.ScreenUpdating = False

        CopyFrom.Copy
        PasteTo.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False

    Application.ScreenUpdating = True

End Sub

Sub Apply_Filter()

    With ThisWorkbook.Sheets("Sheet1")

        Call Copy_Val(.Range("E1792"), .Range("C1799"))

    End With

End Sub

answered May 28, 2019 at 12:17

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Pass Range Cells for Copy-Paste: Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related