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

vlacombe

New Member
Joined
Oct 4, 2019
Messages
31
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
Joined
Oct 4, 2019
Messages
31
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

Threads
1,085,834
Messages
5,386,226
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top