Copy Number Formats
 

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

Copy Number Formats
 

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

Copy Number Formats
 

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

Copy Number Formats
 

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

Copy Number Formats
 

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

Copy Number Formats
 

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

Copy Number Formats
 

I'm running XL2002 (XP). I don't get an error.

If I had, I wouldn't have posted the comment. As I tried to imply, I thought
it strange that there was no error.

Evidently it doesn't give an error for the OP, either. I believe he asked why
it didn't work. I assumed that meant only that the formats weren't copied, and
he would have said so if it produced a run-time error.

BTW, another solution to this is to Edit/Copy the range, followed by 2
PasteSpecial statements, one that copies the values, the other that copies the
formats.


On Tue, 12 Oct 2004 05:57:27 -0700, keepITcool wrote:

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

Copy Number Formats
 

xl2002 (sp2):

I formatted a cell as number, 2 decimals & comma and ran this with just that one
cell selected:

Debug.Print ActiveCell.NumberFormat
ActiveCell.NumberFormat = Null
Debug.Print ActiveCell.NumberFormat

I got this in the immediate window:
#,##0.00
#,##0.00

I changed to a cell formatted as general and got this:
General
General

I formatted A1 as number, 2 decimals & comma and selected a1:a10 (a2:a10 were
still General):

I got this:
Null
General

All the cells in the selection were changed to general.

Well, I thought it was slightly interesting <bg.





Myrna Larson wrote:

I'm running XL2002 (XP). I don't get an error.

If I had, I wouldn't have posted the comment. As I tried to imply, I thought
it strange that there was no error.

Evidently it doesn't give an error for the OP, either. I believe he asked why
it didn't work. I assumed that meant only that the formats weren't copied, and
he would have said so if it produced a run-time error.

BTW, another solution to this is to Edit/Copy the range, followed by 2
PasteSpecial statements, one that copies the values, the other that copies the
formats.

On Tue, 12 Oct 2004 05:57:27 -0700, keepITcool wrote:

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







--

Dave Peterson

Copy Number Formats
 

I think it's interesting, too. Evidently if you try to apply a Null format to
a single cell, it just ignores you. If you try to apply it to a multiple cell
selection, it clears the format. Strange...

On Tue, 12 Oct 2004 19:58:25 -0500, Dave Peterson wrote:

xl2002 (sp2):

I formatted a cell as number, 2 decimals & comma and ran this with just that
one
cell selected:

Debug.Print ActiveCell.NumberFormat
ActiveCell.NumberFormat = Null
Debug.Print ActiveCell.NumberFormat

I got this in the immediate window:
#,##0.00
#,##0.00

I changed to a cell formatted as general and got this:
General
General

I formatted A1 as number, 2 decimals & comma and selected a1:a10 (a2:a10 were
still General):

I got this:
Null
General

All the cells in the selection were changed to general.

Well, I thought it was slightly interesting <bg.





Myrna Larson wrote:

I'm running XL2002 (XP). I don't get an error.

If I had, I wouldn't have posted the comment. As I tried to imply, I
thought
it strange that there was no error.

Evidently it doesn't give an error for the OP, either. I believe he asked
why
it didn't work. I assumed that meant only that the formats weren't copied,
and
he would have said so if it produced a run-time error.

BTW, another solution to this is to Edit/Copy the range, followed by 2
PasteSpecial statements, one that copies the values, the other that copies
the
formats.

On Tue, 12 Oct 2004 05:57:27 -0700, keepITcool
wrote:

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

Copy Number Formats
 

Me: Doctor, Doctor. It hurts when I do this.
Dr: Don't do that!

(Well, that's the lesson I learned!)

Myrna Larson wrote:

I think it's interesting, too. Evidently if you try to apply a Null format to
a single cell, it just ignores you. If you try to apply it to a multiple cell
selection, it clears the format. Strange...

On Tue, 12 Oct 2004 19:58:25 -0500, Dave Peterson wrote:

xl2002 (sp2):

I formatted a cell as number, 2 decimals & comma and ran this with just that
one
cell selected:

Debug.Print ActiveCell.NumberFormat
ActiveCell.NumberFormat = Null
Debug.Print ActiveCell.NumberFormat

I got this in the immediate window:
#,##0.00
#,##0.00

I changed to a cell formatted as general and got this:
General
General

I formatted A1 as number, 2 decimals & comma and selected a1:a10 (a2:a10 were
still General):

I got this:
Null
General

All the cells in the selection were changed to general.

Well, I thought it was slightly interesting <bg.





Myrna Larson wrote:

I'm running XL2002 (XP). I don't get an error.

If I had, I wouldn't have posted the comment. As I tried to imply, I
thought
it strange that there was no error.

Evidently it doesn't give an error for the OP, either. I believe he asked
why
it didn't work. I assumed that meant only that the formats weren't copied,
and
he would have said so if it produced a run-time error.

BTW, another solution to this is to Edit/Copy the range, followed by 2
PasteSpecial statements, one that copies the values, the other that copies
the
formats.

On Tue, 12 Oct 2004 05:57:27 -0700, keepITcool
wrote:

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







--

Dave Peterson