Format lost when re-opening VBA created workbook

[Jon_H]

Hi all,

I've created some coding to create a number of worksheets, to then move each sheet to its own workbook and save the file in a specific location. It works fine but for one small annoying problem.

There's a cell that contains a number format which is present as the file is saved; it formats the number to -£0.00 when I re-open the file the format is changed to £(0.00).

The code I'm using is as follows -

    Sheets("XYZ").Select    
    Sheets("XYZ").Copy After:=Sheets(4)
    Sheets("XYZ").Select
    Range("A5").Select
    Selection.Copy
    Sheets("XYZ").Select
    Range("J30").Select
    Selection.PasteSpecial Paste:=xlPasteValues
        Range("J21").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Name = ActiveSheet.Range("J21").Value
    ActiveSheet.Select
    Cells.Select
    Range("H1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    ActiveSheet.Move


With ActiveWorkbook
    .SaveAs Filename:="C:\XYZ\XYZ\XYZ\XYZ\XYZ\XYZ" & "\" & .Sheets(1).Name & ".xlsx"
    .Close savechanges:=False
    
    End With

Can anyone suggest what's causing this and how I can remedy it please?

Thanks in advance!

Jon

 

[MrTeeny]

I'd guess the cell's format is just being displayed as £(0.00) because that's the global formatting of currency in your excel template files. Looks like your code is only copying xlPasteValues i.e. values of the cells if you also wanted any formatting to get copied also you could use xlPasteValuesAndNumberFormats or just use VBA to format any cells you wanted set.

 

[Jon_H]

I'd guess the cell's format is just being displayed as £(0.00) because that's the global formatting of currency in your excel template files. Looks like your code is only copying xlPasteValues i.e. values of the cells if you also wanted any formatting to get copied also you could use xlPasteValuesAndNumberFormats or just use VBA to format any cells you wanted set.

Thanks for the reply MrTeeny; I've added xlPasteValuesAndNumberFormats and also added a new copy & paste formats after moving the worksheet to a new workbook but I'm still getting the same result i.e. when I open up the newly created workbook it's formatted incorrectly. Any other ideas?

Thanks,

Jon