CODEWITHSUNDEEP
vbaexcel
indranielgupta potta
indranielgupta potta

Reputation: 29

VBA to paste value using Range.Copy method with Offset

I am currently using the following code to consolidate data from Multiple sheets with the same range using Range.Copy method using Offset.

I am trying to Paste only Values instead of formulas. But, I get the formulas also which is leading to Error "#REF!". Can anyone please help me with the correct syntax? I just started learning VBA coding.

    For Each ws In Sheets(Array("A", "B", "C", "D", "E"))
    ws.Activate
    bottomD = Range("BC" & Rows.Count).End(xlUp).Row
    Range("BC3:BE" & bottomD).Copy Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    Next ws

Upvotes: 1

Views: 6527

Answers (4)

A.S.H
A.S.H

Reputation: 29352

You can directly assign values, not need to use copy/paste and no need to select/activate:

For Each ws In Sheets(Array("A", "B", "C", "D", "E"))
    With ws.Range("BE3", ws.Cells(ws.Rows.Count, "BC").End(xlUp))
        Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) _
           .Resize(.Rows.Count, .Columns.Count).value = .value
    End With
Next ws

Upvotes: 2

Rich Holton
Rich Holton

Reputation: 682

You need to use the .PasteSpecial method:

Range("BC3:BE" & bottomD).Copy
Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).pastespecial xlPasteValues

Upvotes: 0

SJR
SJR

Reputation: 23081

You can do this without activating each sheet, and using pastespecial to copy values only

Sub x()

Dim ws As Worksheet, bottomD As Long

For Each ws In Sheets(Array("A", "B", "C", "D", "E"))
    bottomD = ws.Range("BC" & ws.Rows.Count).End(xlUp).Row
    ws.Range("BC3:BE" & bottomD).Copy
    Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlValues
Next ws

End Sub

Upvotes: 2

addohm
addohm

Reputation: 2475

This should get you what you need.

.PasteSpecial xlPasteValues

https://msdn.microsoft.com/en-us/library/office/ff839476.aspx?f=255&MSPPError=-2147217396

Upvotes: 0

Related Questions