Copy to specified sheet, values only

  • Thread starter Thread starter PVANS
  • Start date Start date
P

PVANS

Good morning

I hope someone can help me with this. I am currently using the following
code to copy the entire data from one sheet, and paste it below the previous
data in a different sheet:

Set srcsht = Sheets("Working")
Set dstsht = Sheets("All Trades")
LastrowA = srcsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = dstsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
srcsht.Range("A1:A" & LastrowA).EntireRow.Copy dstsht.Cells(LastrowB, 1)

However, I need the macro to only paste the values, not the formulas, data
etc.
Ordinarily, with a normal copy/paste macro, I would simply add:
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

But it does not let me.

Can someone assist me with this?

Thank you so much!

Kind regards,
Paul
 
Hi Paul

srcsht.Range("A1:A" & LastrowA).EntireRow.Copy
dstsht.Cells(LastrowB, 1).Paste:=xlPasteValues
 
Hi,

try it like this

Set srcsht = Sheets("Working")
Set dstsht = Sheets("All Trades")
LastrowA = srcsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = dstsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
srcsht.Range("A1:A" & LastrowA).EntireRow.Copy
dstsht.Cells(LastrowB, 1).PasteSpecial
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
oops,

I meant

Set srcsht = Sheets("Working")
Set dstsht = Sheets("All Trades")
LastrowA = srcsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = dstsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
srcsht.Range("A1:A" & LastrowA).EntireRow.Copy
dstsht.Cells(LastrowB, 1).PasteSpecial Paste:=xlPasteValues
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Mike,
you also omitted to add Application.CutCopymode = False to remove the
"marching ants"
 
Back
Top