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:
ebUwRIQ.png


Seperate excel file obtained with the script shown above:
y3SLPSw.png


Am I missing something?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top