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


--
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

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

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

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