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