Ron de Bruin - Using PasteSpecial (xlPasteValues) instead of Copy

[Bob]

Ron,
Sorry to bother you, but I need to change the following line in the macro
you sent me:

..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
+ 1, "A")

from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
you kindly tell me how to change it? Thanks for your help.
Regards, Bob

[Dave Peterson]

Maybe ...

..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy

DestSh.Cells(Last + 1, "A").pastespecial paste:=xlpastevalues

Two lines--no continuation characters.

Bob wrote:
>
> Ron,
> Sorry to bother you, but I need to change the following line in the macro
> you sent me:
>
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> + 1, "A")
>
> from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> you kindly tell me how to change it? Thanks for your help.
> Regards, Bob

--

Dave Peterson

[Tom Ogilvy]

..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy

DestSh.Cells(Last + 1, "A").Pastespecial xlValues

would be my thought.

--
Regards,
Tom Ogilvy


"Bob" wrote:

> Ron,
> Sorry to bother you, but I need to change the following line in the macro
> you sent me:
>
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> + 1, "A")
>
> from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> you kindly tell me how to change it? Thanks for your help.
> Regards, Bob
>

[Bob]

Tom,
I tried that, but got an error message. Thanks all the same.
Bob


"Tom Ogilvy" wrote:

> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy
>
> DestSh.Cells(Last + 1, "A").Pastespecial xlValues
>
> would be my thought.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Bob" wrote:
>
> > Ron,
> > Sorry to bother you, but I need to change the following line in the macro
> > you sent me:
> >
> > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> > + 1, "A")
> >
> > from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> > you kindly tell me how to change it? Thanks for your help.
> > Regards, Bob
> >

[Bob]

Dave,
That did the trick! Thanks a million!!!
Regards, Bob


"Dave Peterson" wrote:

> Maybe ...
>
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy
>
> DestSh.Cells(Last + 1, "A").pastespecial paste:=xlpastevalues
>
> Two lines--no continuation characters.
>
> Bob wrote:
> >
> > Ron,
> > Sorry to bother you, but I need to change the following line in the macro
> > you sent me:
> >
> > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> > + 1, "A")
> >
> > from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> > you kindly tell me how to change it? Thanks for your help.
> > Regards, Bob
>
> --
>
> Dave Peterson
>

[Ron de Bruin]

Use this Bob

With sh.Range("A8").CurrentRegion

.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy

With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
End With

End With


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Bob" <[email protected]> wrote in message news:[email protected]...
> Ron,
> Sorry to bother you, but I need to change the following line in the macro
> you sent me:
>
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> + 1, "A")
>
> from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> you kindly tell me how to change it? Thanks for your help.
> Regards, Bob
>

[Bob]

Ron,
Thanks!!! You are a lifesaver!
Regards, Bob


"Ron de Bruin" wrote:

> Use this Bob
>
> With sh.Range("A8").CurrentRegion
>
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy
>
> With DestSh.Cells(Last + 1, "A")
> .PasteSpecial xlPasteValues, , False, False
> Application.CutCopyMode = False
> End With
>
> End With
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Bob" <[email protected]> wrote in message news:[email protected]...
> > Ron,
> > Sorry to bother you, but I need to change the following line in the macro
> > you sent me:
> >
> > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> > + 1, "A")
> >
> > from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> > you kindly tell me how to change it? Thanks for your help.
> > Regards, Bob
> >
>
>
>

[Dave Peterson]

You have your solution, but are you sure Tom's suggestion didn't work.

It looks very close to the others.

Bob wrote:
>
> Tom,
> I tried that, but got an error message. Thanks all the same.
> Bob
>
> "Tom Ogilvy" wrote:
>
> > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy
> >
> > DestSh.Cells(Last + 1, "A").Pastespecial xlValues
> >
> > would be my thought.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Bob" wrote:
> >
> > > Ron,
> > > Sorry to bother you, but I need to change the following line in the macro
> > > you sent me:
> > >
> > > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> > > + 1, "A")
> > >
> > > from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> > > you kindly tell me how to change it? Thanks for your help.
> > > Regards, Bob
> > >

--

Dave Peterson

[Tom Ogilvy]

Must be you Bob. Worked fine for me. And you said essentially identical
code by Dave works.

--
Regards,
Tom Ogilvy


"Bob" wrote:

> Tom,
> I tried that, but got an error message. Thanks all the same.
> Bob
>
>
> "Tom Ogilvy" wrote:
>
> > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy
> >
> > DestSh.Cells(Last + 1, "A").Pastespecial xlValues
> >
> > would be my thought.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Bob" wrote:
> >
> > > Ron,
> > > Sorry to bother you, but I need to change the following line in the macro
> > > you sent me:
> > >
> > > .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy DestSh.Cells(Last
> > > + 1, "A")
> > >
> > > from a Copy operation to a PasteSpecial (xlPasteValues) operation. Could
> > > you kindly tell me how to change it? Thanks for your help.
> > > Regards, Bob
> > >