I must be missing something obvious here... I am trying to use a rang
copy to copy values and formats from a series of worksheets. Th
values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol)
sh.Cells(shLast, LastCol))
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
.Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
.Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Stev

--
Steve
-----------------------------------------------------------------------
SteveS's Profile: http://www.excelforum.com/member.php...fo&userid=1011
View this thread: http://www.excelforum.com/showthread.php?threadid=26838

I expect the underlying problem is that in your source range, the number
format varies from one cell to another. In that situation, the .Numberformat
property returns NULL. It doesn't return an array of formats for each cell in
the range.

To demonstrate: put some data in, say A1:A3, and set different number formats
for at least one of the cells. Then select those cells, in the VBE immediate
window type ? Selection.Numberformat. You'll see that it prints NULL.

If the formats vary, and you want to replicate them, you'll have to do it a
cell at a time, or at least do it in "chunks" of cells, all of which have the
same number format.


On Mon, 11 Oct 2004 23:54:45 -0500, SteveS
wrote:


I must be missing something obvious here... I am trying to use a range
copy to copy values and formats from a series of worksheets. The
values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol),
sh.Cells(shLast, LastCol))
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Steve

PS: The interesting thing is that assigning a number format of NULL, as your
code is probably doing, doesn't cause a run-time error.

On Tue, 12 Oct 2004 00:12:28 -0500, Myrna Larson
wrote:

I expect the underlying problem is that in your source range, the number
format varies from one cell to another. In that situation, the .Numberformat
property returns NULL. It doesn't return an array of formats for each cell in
the range.

To demonstrate: put some data in, say A1:A3, and set different number formats
for at least one of the cells. Then select those cells, in the VBE immediate
window type ? Selection.Numberformat. You'll see that it prints NULL.

If the formats vary, and you want to replicate them, you'll have to do it a
cell at a time, or at least do it in "chunks" of cells, all of which have the
same number format.


On Mon, 11 Oct 2004 23:54:45 -0500, SteveS
wrote:


I must be missing something obvious here... I am trying to use a range
copy to copy values and formats from a series of worksheets. The
values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol),
sh.Cells(shLast, LastCol))
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Steve

To OP:
If you're coding for xl2000+ then you could use:

rngSrc.Copy
rngDst.PasteSpecial xlPasteValuesAndNumberFormats

To Myrna:
assigning NULL or a mixed bag of numberformats throws exception in all
versions on my PC (as expected)

i did note that assigning EMPTY clear formats... (not surprisingly)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Myrna Larson wrote:

PS: The interesting thing is that assigning a number format of NULL,
as your code is probably doing, doesn't cause a run-time error.

On Tue, 12 Oct 2004 00:12:28 -0500, Myrna Larson
wrote:

I expect the underlying problem is that in your source range, the
number format varies from one cell to another. In that situation, the
.Numberformat property returns NULL. It doesn't return an array of
formats for each cell in the range.

To demonstrate: put some data in, say A1:A3, and set different number
formats for at least one of the cells. Then select those cells, in the
VBE immediate window type ? Selection.Numberformat. You'll see that
it prints NULL.

If the formats vary, and you want to replicate them, you'll have to do
it a cell at a time, or at least do it in "chunks" of cells, all of
which have the same number format.


On Mon, 11 Oct 2004 23:54:45 -0500, SteveS
wrote:


I must be missing something obvious here... I am trying to use a
range copy to copy values and formats from a series of worksheets.
The values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol), sh.Cells(shLast,
LastCol)) DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Steve

assigning NULL or a mixed bag of numberformats throws exception in all
versions on my PC (as expected)

Like Myrna, assigning a number format of NULL does not error for me.

Selection.NumberFormat = Null

With identical formats this does nothing, ie original format retained. With
mixed number formats all are changed to General. But no error.

Regards,
Peter


"keepITcool" wrote in message
...
To OP:
If you're coding for xl2000+ then you could use:

rngSrc.Copy
rngDst.PasteSpecial xlPasteValuesAndNumberFormats

To Myrna:
assigning NULL or a mixed bag of numberformats throws exception in all
versions on my PC (as expected)

i did note that assigning EMPTY clear formats... (not surprisingly)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Myrna Larson wrote:

PS: The interesting thing is that assigning a number format of NULL,
as your code is probably doing, doesn't cause a run-time error.

On Tue, 12 Oct 2004 00:12:28 -0500, Myrna Larson
wrote:

I expect the underlying problem is that in your source range, the
number format varies from one cell to another. In that situation, the
.Numberformat property returns NULL. It doesn't return an array of
formats for each cell in the range.

To demonstrate: put some data in, say A1:A3, and set different number
formats for at least one of the cells. Then select those cells, in the
VBE immediate window type ? Selection.Numberformat. You'll see that
it prints NULL.

If the formats vary, and you want to replicate them, you'll have to do
it a cell at a time, or at least do it in "chunks" of cells, all of
which have the same number format.


On Mon, 11 Oct 2004 23:54:45 -0500, SteveS
wrote:


I must be missing something obvious here... I am trying to use a
range copy to copy values and formats from a series of worksheets.
The values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol), sh.Cells(shLast,
LastCol)) DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Steve

correction:
xlXP and xl2003 throw error.
xl97 accepts

selection.numberformat = Null


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Peter T" <peter_t@discussions wrote:

assigning NULL or a mixed bag of numberformats throws exception in
all versions on my PC (as expected)

Like Myrna, assigning a number format of NULL does not error for me.

Selection.NumberFormat = Null

With identical formats this does nothing, ie original format retained.
With mixed number formats all are changed to General. But no error.

Regards,
Peter


"keepITcool" wrote in message
...
To OP:
If you're coding for xl2000+ then you could use:

rngSrc.Copy
rngDst.PasteSpecial xlPasteValuesAndNumberFormats

To Myrna:
assigning NULL or a mixed bag of numberformats throws exception in
all versions on my PC (as expected)

i did note that assigning EMPTY clear formats... (not surprisingly)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Myrna Larson wrote:

PS: The interesting thing is that assigning a number format of
NULL, as your code is probably doing, doesn't cause a run-time
error.

On Tue, 12 Oct 2004 00:12:28 -0500, Myrna Larson
wrote:

I expect the underlying problem is that in your source range, the
number format varies from one cell to another. In that situation,
the .Numberformat property returns NULL. It doesn't return an array
of formats for each cell in the range.

To demonstrate: put some data in, say A1:A3, and set different
number formats for at least one of the cells. Then select those
cells, in the VBE immediate window type ? Selection.Numberformat.
You'll see that it prints NULL.

If the formats vary, and you want to replicate them, you'll have to
do it a cell at a time, or at least do it in "chunks" of cells, all
of which have the same number format.


On Mon, 11 Oct 2004 23:54:45 -0500, SteveS
wrote:


I must be missing something obvious here... I am trying to use a
range copy to copy values and formats from a series of worksheets.
The values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol), sh.Cells(shLast,
LastCol)) DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Steve