Copy Number Formats

[SteveS]

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

 

[Myrna Larson]

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.

 

[Myrna Larson]

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.

 

[keepITcool]

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 >

 

[Peter T]

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]

correction:
xlXP and xl2003 throw error.
xl97 accepts

selection.numberformat = Null


keepITcool

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

 

[Myrna Larson]

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.

 

[Dave Peterson]

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]

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

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

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.

 

[Dave Peterson]

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

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

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

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