After saving the invoice with new name using VBA code colors and format change

[poras]

Hi All,

i was trying to generate the auto number and save the invoice by using the vba module. The code is below. It works ok but the only problem is that after creating the new file all colors and cell format automatically change. I want to have the same format in new file also can any body help me in this regard.

Sub NextInvoice()
    Range("G8").Value = Range("G8").Value + 1
    Range("B13:G17,B20:G27").ClearContents
End Sub

Sub SaveInvWithNewName()
    Dim NewFN As Variant
    ' Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = "C:\Users\Finance\Documents\Data\DIS Data\Inv" & Range("G8").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub

[JapanDave]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here