VBA macro to save different sheets as seperate excel file changes my cell format?


New Member
Oct 4, 2019
Hello, I'm currently generating sheets based off 3 templates

For the most part (not all but almost the exact same range for all templates)
J21 to J50 is set as accounting

When I generate my sheets, all the ones created still have J21 to J50 format set to accounting

Then I use this script to save each SOUMISSION sheets seperatly
Sub Save_sheets_FINAL()  Dim sh As Worksheet, i As Long, shs As Variant
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  shs = Array("SOUMISSION")
  For Each sh In Sheets
    For i = 0 To UBound(shs)
      If LCase(sh.Name) Like "*" & LCase(shs(i)) & "*" Then
         ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sh.Name & ".xlsx"
         ActiveWorkbook.Close False
        Exit For
      End If
End Sub
But then when I open the newly saved excel. J21 to J50 is set to Custom and the view is all messed up...
See capture here:

Newly generated sheets with proper format:

Seperate excel file obtained with the script shown above:

Am I missing something?


New Member
Oct 4, 2019
Also, whenever I open these seperately saved sheets, it always ask me to save any modification even though I mate no change at all...
I can simply open and close them right away and it pops the save modification message.

Forum statistics

Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...