Run one macro for all macros instead of doing it one by one

[krodriguez]

Hello,

I have the following code with 2 macros and will like to run it all at once, how can I do this? Thanks

Sub SaveSheetsAsFiles()
Dim SheetsToSave
SheetsToSave = Array("Rainbow", "RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling", "RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo") 'change tab names to suit
Application.ScreenUpdating = False
For Each sht In Sheets(SheetsToSave)
sht.Copy
'file location is same as workbook the code is in. Change to suit
'file format is .xlsm - change to suit
ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsm", FileFormat:=52
Next sht
Application.ScreenUpdating = True
End Sub


Sub SaveSheetsAsFiles2()
Dim SheetsToSave
SheetsToSave = Array("Neocell", "NC-Net Realization", "NC-Red Rev", "NC-COGS NC-Logistics", "NC-R&D", "NC-Selling", "NC-Administrative", "NC-Advertising", "NC-Sales Promo") 'change tab names to suit
Application.ScreenUpdating = False
For Each sht In Sheets(SheetsToSave)
sht.Copy
'file location is same as workbook the code is in. Change to suit
'file format is .xlsm - change to suit
ActiveWorkbook.SaveAs Filename:=sht.Name & ".xlsm", FileFormat:=52
Next sht
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Ok, how about

Sub SaveSheetsAsFiles()
   Dim SheetsToSave1
   Dim SheetsToSave2

   SheetsToSave1 = Array("Rainbow", "RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling", "RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo")
   SheetsToSave2 = Array("Neocell", "NC-Net Realization", "NC-Red Rev", "NC-COGS", "NC-Logistics", "NC-R&D", "NC-Selling", "NC-Administrative", "NC-Advertising", "NC-Sales Promo")
   
   Application.ScreenUpdating = False
   Sheets(SheetsToSave1).Copy
   ActiveWorkbook.SaveAs Filename:=SheetsToSave1(0) & ".xlsm", FileFormat:=52
   ActiveWorkbook.Close False
   
   Sheets(SheetsToSave2).Copy
   ActiveWorkbook.SaveAs Filename:=SheetsToSave2(0) & ".xlsm", FileFormat:=52
   ActiveWorkbook.Close False
   Application.ScreenUpdating = True
End Sub

 

[krodriguez]

It works perfect! thanks very much for you assistance.

If I want to change the location of where the files get saved, how can I do that? looks like by default they get saved on my documents folder...

 

[Fluff]

Try

Sub SaveSheetsAsFiles()
   Dim SheetsToSave1, SheetsToSave2
   Dim Pth As String

   Pth = "[COLOR=#ff0000]C:\Mrexcel\[/COLOR]"
   SheetsToSave1 = Array("Rainbow", "RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling", "RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo")
   SheetsToSave2 = Array("Neocell", "NC-Net Realization", "NC-Red Rev", "NC-COGS", "NC-Logistics", "NC-R&D", "NC-Selling", "NC-Administrative", "NC-Advertising", "NC-Sales Promo")
   
   Application.ScreenUpdating = False
   Sheets(SheetsToSave1).Copy
   ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave1(0) & ".xlsm", FileFormat:=52
   ActiveWorkbook.Close False
   
   Sheets(SheetsToSave2).Copy
   ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave2(0) & ".xlsm", FileFormat:=52
   ActiveWorkbook.Close False
   Application.ScreenUpdating = True
End Sub

Change the part in red to match you file path

 

[krodriguez]

awesome! thanks so much!

 

[Fluff]

Glad to help & thanks for the feedback

 

[krodriguez]

Is there a way to speed up the macro? I added additional arrays so now instead of 2 workbooks have 10, but it takes about 3-4mins to create just 1 workbook.

 

[Fluff]

You can try adding these

   [COLOR=#0000ff]With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
      .EnableEvents = False
   End With[/COLOR]
   Sheets(SheetsToSave1).Copy
   ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave1(0) & ".xlsm", FileFormat:=52
   ActiveWorkbook.Close False
   
   Sheets(SheetsToSave2).Copy
   ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave2(0) & ".xlsm", FileFormat:=52
   ActiveWorkbook.Close False
   [COLOR=#0000ff]With Application
      .ScreenUpdating = True
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
   End With
[/COLOR]

 

[krodriguez]

Should the code read as follows?


Sub SavePLsForAllBrands()
With application
.Screenupdating = true
.Calculation = xCalculationManual
.EnableEvents = False
End with


Dim SheetsToSave1, SheetsToSave2, SheetsToSave3, SheetsToSave4, SheetsToSave5, SheetsToSave6, SheetsToSave7, SheetsToSave8, SheetsToSave9, SheetsToSave10
Dim Pth As String


Pth = "P:\Financial Planning and Analysis\2019 Forecast\01 - July\Development"
SheetsToSave1 = Array("Rainbow", "RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling", "RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo")
SheetsToSave2 = Array("Neocell", "NC-Net Realization", "NC-Red Rev", "NC-COGS", "NC-Logistics", "NC-R&D", "NC-Selling", "NC-Administrative", "NC-Advertising", "NC-Sales Promo")
SheetsToSave3 = Array("Natural Vitality", "NV-Net Realization", "NV-Red Rev", "NV-COGS", "NV-Logistics", "NV-R&D", "NV-Selling", "NV-Administrative", "NV-Advertising", "NV-Sales Promo")
SheetsToSave4 = Array("Champion", "CP-Net Realization", "CP-Red Rev", "CP-COGS", "CP-Logistics", "CP-R&D", "CP-Selling", "CP-Administrative", "CP-Advertising", "CP-Sales Promo")
SheetsToSave5 = Array("Private Label", "PL-Net Realization", "PL-Red Rev", "PL-COGS", "PL-Logistics", "PL-R&D", "PL-Selling", "PL-Administrative", "PL-Advertising", "PL-Sales Promo")
SheetsToSave6 = Array("Contract Manuf", "CM-Net Realization", "CM-Red Rev", "CM-COGS", "CM-Logistics", "CM-R&D", "CM-Administrative")
SheetsToSave7 = Array("Stop Aging Now", "SAN-Net Realization", "SAN-Red Rev", "SAN-COGS", "SAN-Logistics", "SAN-R&D", "SAN-Selling", "SAN-Administrative", "SAN-Advertising", "SAN-Sales Promo", "Vitamin Research")
SheetsToSave8 = Array("True Health", "TM-Net Realization", "TM-Red Rev", "TM-COGS", "TM-Logistics", "TM-R&D", "TM-Selling", "TM-Administrative", "TM-Advertising", "TM-Sales Promo")
SheetsToSave9 = Array("Blessed Herbs", "BH-Net Realization", "BH-Red Rev", "BH-COGS", "BH-Logistics", "BH-R&D", "BH-Selling", "BH-Administrative", "BH-Advertising", "BH-Sales Promo")

Application.ScreenUpdating = False
Sheets(SheetsToSave1).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave1(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave2).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave2(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave3).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave3(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave4).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave4(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave5).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave5(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave6).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave6(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave7).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave7(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave8).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave8(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Sheets(SheetsToSave9).Copy
ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave9(0) & ".xlsm", FileFormat:=52
ActiveWorkbook.Close False

Application.ScreenUpdating = True

With application
.Screenupdating = true
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End with


End Sub

 

[Fluff]

You were missing an l from xlcalculationManual, but otherwise that's fine.
Alternatively you can simplify things slightly like

Sub SavePLsForAllBrands()
   With Application
      .ScreenUpdating = True
      .Calculation = xlCalculationManual
      .EnableEvents = False
   End With
   
   Dim i As Long
   Dim SheetsToSave(8)
   Dim Pth As String
   
   
   Pth = "P:\Financial Planning and Analysis\2019 Forecast\01 - July\Development"
   SheetsToSave(0) = Array("pcode", "sheet1") 'Array("Rainbow", "RLN-Net Realization", "RLN-Red Rev", "RLN-COGS", "RLN-Logistics", "RLN-R&D", "RLN-Selling", "RLN-Administrative", "RLN-Advertising", "RLN-Sales Promo")
   SheetsToSave(1) = Array("Neocell", "NC-Net Realization", "NC-Red Rev", "NC-COGS", "NC-Logistics", "NC-R&D", "NC-Selling", "NC-Administrative", "NC-Advertising", "NC-Sales Promo")
   SheetsToSave(2) = Array("Natural Vitality", "NV-Net Realization", "NV-Red Rev", "NV-COGS", "NV-Logistics", "NV-R&D", "NV-Selling", "NV-Administrative", "NV-Advertising", "NV-Sales Promo")
   SheetsToSave(3) = Array("Champion", "CP-Net Realization", "CP-Red Rev", "CP-COGS", "CP-Logistics", "CP-R&D", "CP-Selling", "CP-Administrative", "CP-Advertising", "CP-Sales Promo")
   SheetsToSave(4) = Array("Private Label", "PL-Net Realization", "PL-Red Rev", "PL-COGS", "PL-Logistics", "PL-R&D", "PL-Selling", "PL-Administrative", "PL-Advertising", "PL-Sales Promo")
   SheetsToSave(5) = Array("Contract Manuf", "CM-Net Realization", "CM-Red Rev", "CM-COGS", "CM-Logistics", "CM-R&D", "CM-Administrative")
   SheetsToSave(6) = Array("Stop Aging Now", "SAN-Net Realization", "SAN-Red Rev", "SAN-COGS", "SAN-Logistics", "SAN-R&D", "SAN-Selling", "SAN-Administrative", "SAN-Advertising", "SAN-Sales Promo", "Vitamin Research")
   SheetsToSave(7) = Array("True Health", "TM-Net Realization", "TM-Red Rev", "TM-COGS", "TM-Logistics", "TM-R&D", "TM-Selling", "TM-Administrative", "TM-Advertising", "TM-Sales Promo")
   SheetsToSave(8) = Array("Blessed Herbs", "BH-Net Realization", "BH-Red Rev", "BH-COGS", "BH-Logistics", "BH-R&D", "BH-Selling", "BH-Administrative", "BH-Advertising", "BH-Sales Promo")
   
   For i = 0 To UBound(SheetsToSave)
      Sheets(SheetsToSave(i)).Copy
      ActiveWorkbook.SaveAs Filename:=Pth & SheetsToSave(i)(0) & ".xlsm", FileFormat:=52
      ActiveWorkbook.Close False
   Next i
   
   With Application
      .ScreenUpdating = True
      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
   End With
End Sub

 

[krodriguez]

Thank you. Something else that I would like to add to this code is break links, my arrays (sheets to save 0 thru 8) shows the links after they get saved but I don't need links, how can we break them?