Search code examples
excelvbanumber-formatting

With Excel VBA is there a way to copy an entire worksheet's NumberFormat to a new worksheet?

In Excel is there a way to copy the NumberFormat from an input range with multiple columns to an output range of the same number of columns?

I've tried the several variants in this code sample but there's either no change to the output range's formatting when I try to source it from multiple columns, or the performance of copying the values is too problematic.

Sub Import()
    'Demonstration variables
    Dim ws_input As Worksheet: Set ws_input = ThisWorkbook.Sheets(1)
    Dim ws_output As Worksheet: Set ws_output = ThisWorkbook.Sheets(2)
    Dim rowLast As Double, colLast As Double, i As Double

    rowLast = ws_input.Cells(Rows.Count, 1).End(xlUp).Row
    colLast = ws_input.Cells(1, Columns.Count).End(xlToLeft).Column

    'This variant is way too much of a performance hit and Excel can misapply formatting
    'E.g. Cell XFD1048576 is considered used
    ws_input.Cells.Copy Destination:=ws_output.Range("A1")

    'This variant can copy "###" as the value if the input cell widths are too small
    ws_output.Range("A1", Cells(rowLast, colLast).Address).Value = _
        ws_input.Range("A1", Cells(rowLast, colLast).Address).Value

    'This variant copies values correctly but without formatting
    ws_output.Range("A1", Cells(rowLast, colLast).Address).Value2 = _
        ws_input.Range("A1", Cells(rowLast, colLast).Address).Value2
    ws_output.Range("A1", Cells(rowLast, colLast).Address).NumberFormat  = _
        ws_input.Range("A1", Cells(rowLast, colLast).Address).NumberFormat

    'This variant also seemingly does nothing with the NumberFormat
    ws_output.Range("A1", Cells(rowLast, colLast).Address).EntireColumn.NumberFormat  = _
        ws_input.Range("A1", Cells(rowLast, colLast).Address).EntireColumn.NumberFormat

    'This variant also seemingly does nothing with the NumberFormat
    ws_output.Range("A1").CurrentRegion.NumberFormat = _
        ws_input.Range("A1").CurrentRegion.NumberFormat

    'Kludgy solution I've come to
    For i = 1 to colLast
        ws_output.Columns(i).NumberFormat = ws_input.Columns(i).NumberFormat
    Next i
End Sub

I'm hoping to avoid having to use an iterator every time I need to apply number formatting, and it seems like there should be a more intuitive way that Excel can apply the NumberFormat property to multiple ranges from multiple ranges. If I only wanted to source one range as the format, ws_output.Range("A1:Z9999").NumberFormat = ws_input.Range("A1").NumberFormat works, but it seems to break down as soon as multiple column sources are used.

Thank you for your time! I greatly appreciate any help on this topic.

Solution

  • From the Range.NumberFormat docs:

    This property returns Null if all cells in the specified range don't have the same number format.

    If your columns have different number formats, then you'll have to loop over them as in your last approach, assuming you do not want to use the clipboard.