Excel 97: Copy Range with all formatting to another Sheet
 

Hello all,

just want to copy the Range from one Sheet to another one, values and
all formats, perfect would be even the column width. When I copy and
paste manually, all is working perfect, but not with VBA. Be aware
that there are joined cells in my sheet. Any hints?


Thanks and greetings
Udo

Excel 97: Copy Range with all formatting to another Sheet
 

Sub test2()
Dim rFrom As Range, rTo As Range

Set rFrom = ActiveWorkbook.Worksheets("Sheet1").Range("A1:B10" )
Set rTo = ActiveWorkbook.Worksheets("Sheet2").Range("C1")

rFrom.Copy rTo

End Sub

This method will not copy row/column dimensions. I don't know what you mean
by "joined cells".

Regards,
Peter T

"Udo" wrote in message
...
Hello all,

just want to copy the Range from one Sheet to another one, values and
all formats, perfect would be even the column width. When I copy and
paste manually, all is working perfect, but not with VBA. Be aware
that there are joined cells in my sheet. Any hints?


Thanks and greetings
Udo

Excel 97: Copy Range with all formatting to another Sheet
 

Udo -

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for column
widths.

If by "joined cells" you mean merged cells, I would avoid the many problems
associated with merged cells by redesigning the worksheet so they are not
needed. Often, the horizontal format "center across selection" is an
alternative.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel



"Udo" wrote in message
...
Hello all,

just want to copy the Range from one Sheet to another one, values and
all formats, perfect would be even the column width. When I copy and
paste manually, all is working perfect, but not with VBA. Be aware
that there are joined cells in my sheet. Any hints?


Thanks and greetings
Udo

Excel 97: Copy Range with all formatting to another Sheet
 

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to paste
column widths unless an entire sheet is copied to a new sheet. There is of
course PasteSpecial that can paste values or formats and some other things,
but not row/column dim's.

Regards,
Peter T

Excel 97: Copy Range with all formatting to another Sheet
 

I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths

And it even had a bug. MS didn't create that constant--except in the help. In
xl2k, you would have to use:

range.pastespecial paste:=8



Peter T wrote:

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to paste
column widths unless an entire sheet is copied to a new sheet. There is of
course PasteSpecial that can paste values or formats and some other things,
but not row/column dim's.

Regards,
Peter T

--

Dave Peterson

Excel 97: Copy Range with all formatting to another Sheet
 

Indeed it was introduced in XL2000 but it's not even documented in XL2000's
help at all

"Paste Optional Variant. The part of the range to be pasted. Can be one of
the following XlPasteType constants: xlPasteAll, xlPasteFormulas,
xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders. The
default value is xlPasteAll."

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

However, in XL97 if entire columns are copied then Pastespecial formats will
paste column widths. That requires of course that the destination is one or
same number of columns as the source.

Regards,
Peter T


"Dave Peterson" wrote in message
...
I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths

And it even had a bug. MS didn't create that constant--except in the
help. In
xl2k, you would have to use:

range.pastespecial paste:=8



Peter T wrote:

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to paste
column widths unless an entire sheet is copied to a new sheet. There is
of
course PasteSpecial that can paste values or formats and some other
things,
but not row/column dim's.

Regards,
Peter T

--

Dave Peterson

Excel 97: Copy Range with all formatting to another Sheet
 

Weird, in XL2k a recorded macro returns -

Selection.PasteSpecial Paste:=xlColumnWidths ' etc

But it fails on playback as the named constant is not found

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Indeed it was introduced in XL2000 but it's not even documented in
XL2000's help at all

"Paste Optional Variant. The part of the range to be pasted. Can be one
of the following XlPasteType constants: xlPasteAll, xlPasteFormulas,
xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders.
The default value is xlPasteAll."

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

However, in XL97 if entire columns are copied then Pastespecial formats
will paste column widths. That requires of course that the destination is
one or same number of columns as the source.

Regards,
Peter T


"Dave Peterson" wrote in message
...
I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths

And it even had a bug. MS didn't create that constant--except in the
help. In
xl2k, you would have to use:

range.pastespecial paste:=8



Peter T wrote:

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for
column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to
paste
column widths unless an entire sheet is copied to a new sheet. There is
of
course PasteSpecial that can paste values or formats and some other
things,
but not row/column dim's.

Regards,
Peter T

--

Dave Peterson

Excel 97: Copy Range with all formatting to another Sheet
 

Try using:
Selection.PasteSpecial Paste:=8




Peter T wrote:

Weird, in XL2k a recorded macro returns -

Selection.PasteSpecial Paste:=xlColumnWidths ' etc

But it fails on playback as the named constant is not found

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Indeed it was introduced in XL2000 but it's not even documented in
XL2000's help at all

"Paste Optional Variant. The part of the range to be pasted. Can be one
of the following XlPasteType constants: xlPasteAll, xlPasteFormulas,
xlPasteValues, xlPasteFormats, xlPasteNotes, or xlPasteAllExceptBorders.
The default value is xlPasteAll."

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

However, in XL97 if entire columns are copied then Pastespecial formats
will paste column widths. That requires of course that the destination is
one or same number of columns as the source.

Regards,
Peter T


"Dave Peterson" wrote in message
...
I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths

And it even had a bug. MS didn't create that constant--except in the
help. In
xl2k, you would have to use:

range.pastespecial paste:=8



Peter T wrote:

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for
column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to
paste
column widths unless an entire sheet is copied to a new sheet. There is
of
course PasteSpecial that can paste values or formats and some other
things,
but not row/column dim's.

Regards,
Peter T

--

Dave Peterson


--

Dave Peterson

Excel 97: Copy Range with all formatting to another Sheet
 

Hi Dave,

Try using:
Selection.PasteSpecial Paste:=8

Yes that works, it's just odd that somehow the macro recorder returns the
named constant that's not recognized by xl2k's VBA.

A couple of posts back this

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

should have read

This works in xl2k but fails in xl97
range.pastespecial paste:=8

Regards,
Peter T

"Dave Peterson" wrote in message
...
Try using:
Selection.PasteSpecial Paste:=8




Peter T wrote:

Weird, in XL2k a recorded macro returns -

Selection.PasteSpecial Paste:=xlColumnWidths ' etc

But it fails on playback as the named constant is not found

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Indeed it was introduced in XL2000 but it's not even documented in
XL2000's help at all

"Paste Optional Variant. The part of the range to be pasted. Can be
one
of the following XlPasteType constants: xlPasteAll, xlPasteFormulas,
xlPasteValues, xlPasteFormats, xlPasteNotes, or
xlPasteAllExceptBorders.
The default value is xlPasteAll."

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

However, in XL97 if entire columns are copied then Pastespecial
formats
will paste column widths. That requires of course that the destination
is
one or same number of columns as the source.

Regards,
Peter T


"Dave Peterson" wrote in message
...
I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths

And it even had a bug. MS didn't create that constant--except in the
help. In
xl2k, you would have to use:

range.pastespecial paste:=8



Peter T wrote:

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and
Paste
commands: one for cell values, one for cell formats, and one for
column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to
paste
column widths unless an entire sheet is copied to a new sheet. There
is
of
course PasteSpecial that can paste values or formats and some other
things,
but not row/column dim's.

Regards,
Peter T

--

Dave Peterson


--

Dave Peterson

Excel 97: Copy Range with all formatting to another Sheet
 

I think that even MS would acknowledge this as a bug in xl2k.

Peter T wrote:

Hi Dave,

Try using:
Selection.PasteSpecial Paste:=8

Yes that works, it's just odd that somehow the macro recorder returns the
named constant that's not recognized by xl2k's VBA.

A couple of posts back this

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

should have read

This works in xl2k but fails in xl97
range.pastespecial paste:=8

Regards,
Peter T

"Dave Peterson" wrote in message
...
Try using:
Selection.PasteSpecial Paste:=8




Peter T wrote:

Weird, in XL2k a recorded macro returns -

Selection.PasteSpecial Paste:=xlColumnWidths ' etc

But it fails on playback as the named constant is not found

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Indeed it was introduced in XL2000 but it's not even documented in
XL2000's help at all

"Paste Optional Variant. The part of the range to be pasted. Can be
one
of the following XlPasteType constants: xlPasteAll, xlPasteFormulas,
xlPasteValues, xlPasteFormats, xlPasteNotes, or
xlPasteAllExceptBorders.
The default value is xlPasteAll."

This works in xl2k but fails in xl97
range.pastespecial paste:=xlPasteColumnWidths

However, in XL97 if entire columns are copied then Pastespecial
formats
will paste column widths. That requires of course that the destination
is
one or same number of columns as the source.

Regards,
Peter T


"Dave Peterson" wrote in message
...
I think xl2k added:
range.pastespecial paste:=xlPasteColumnWidths

And it even had a bug. MS didn't create that constant--except in the
help. In
xl2k, you would have to use:

range.pastespecial paste:=8



Peter T wrote:

"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and
Paste
commands: one for cell values, one for cell formats, and one for
column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to
paste
column widths unless an entire sheet is copied to a new sheet. There
is
of
course PasteSpecial that can paste values or formats and some other
things,
but not row/column dim's.

Regards,
Peter T

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Excel 97: Copy Range with all formatting to another Sheet
 

Peter T -

I'll stand corrected, too.

I was likely using separate column autofits instead of column width pastes.

- Mike


"Peter T" <peter_t@discussions wrote in message
...
"Mike Middleton" wrote in message

As I recall, in Excel 97, I had to use three separate Copy and Paste
commands: one for cell values, one for cell formats, and one for column
widths.

I'll stand corrected but I don't think Excel 97 has any facility to paste
column widths unless an entire sheet is copied to a new sheet. There is of
course PasteSpecial that can paste values or formats and some other
things,
but not row/column dim's.

Regards,
Peter T