CODEWITHSUNDEEP
excelvba
Aquaholic
Aquaholic

Reputation: 883

Pass Range Cells for Copy-Paste: Excel VBA

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.

Upvotes: 2

Views: 109

Answers (2)

Greg Viers
Greg Viers

Reputation: 3523

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

Upvotes: 3

Error 1004
Error 1004

Reputation: 8220

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

Upvotes: 1

Related Questions