CODEWITHSUNDEEP
excelvba
Selvam
Selvam

Reputation: 1093

Copy Formula from one workbook, paste as value in the Active workbook

The source range P22:P35 is formula.

This copies the formula:

Sub somemodule()

Set Src = Workbooks("Abc")
Sheets("Thissheet").Select
Src.Sheets("Thatsheet").Range("P22:P35").CopyRange("Q5")

End Sub

I want to paste only values.

I tried this:

Src.Sheets("Thatsheet").Range("P22:P35").CopyRange("Q5").PasteSpecial

It gives Run-time Error '1004'

I tried this:

Src.Sheets("Thatsheet").Range("P22:P35").CopyRange("Q5").PasteSpecialxlPasteValues

It gives compile error: Expected End of statement.

How do I copy values into the active sheet?

Upvotes: 0

Views: 299

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Avoid copy/paste. It's slow, it's tedious, and it's not worth it.

Dim srcRng As Range, tgtRng As Range
Set srcRng = Workbooks("Abc").Worksheets("SheetName").Range("P22:P35")
Set tgtRng = Workbooks("Tgt").Worksheets("SheetName2").Range("P22:P35")
'======================================================================
'====== MAKE SURE YOU USE CORRECT WORKBOOK/WORKSHEET NAMES ABOVE ======
'======================================================================

tgtRng.Value = srcRng.Value

Upvotes: 1

Related Questions