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

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

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

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 - Using PasteSpecial (xlPasteValues) instead of Copy
 

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

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

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

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

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 - Using PasteSpecial (xlPasteValues) instead of Copy
 

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" wrote in message ...
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

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

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" wrote in message ...
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

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

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

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

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