Copy to specified sheet, values only
 

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

Copy to specified sheet, values only
 

Hi Paul

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


--
Regards
Roger Govier

PVANS wrote:
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

Copy to specified sheet, values only
 

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.


"PVANS" wrote:

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

Copy to specified sheet, values only
 

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.


"PVANS" wrote:

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

Copy to specified sheet, values only
 

Mike,
you also omitted to add Application.CutCopymode = False to remove the
"marching ants"
--
jb


"Mike H" wrote:

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.


"PVANS" wrote:

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