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

vlacombe

New Member
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
Code:
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
         sh.Copy
         ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sh.Name & ".xlsx"
         ActiveWorkbook.Close False
        Exit For
      End If
    Next
  Next
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?
 

vlacombe

New Member
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.
 

Some videos you may like

This Week's Hot Topics

Top