Saving a file as an Excel file while looping in VBA

[Symon_J_Turner101]

I have written some code that uses the copy to new sheet function in excel a very format heavy tab. I then have written some code to save said tab and some other code to update some values in the original sheet and alter the country loop through it saving the file each time.

Each bit of the code works but when I run it together it breaks. Half the trouble I think is you can'tr pre name the sheet created by the copy to new workbook function.

This is the saving macro

Sub Save_2_Excel()


Dim Ref As String
Dim St As String
Dim En As String
Dim Ex_Ref As String


FilePath = "P:\Hyundai\Hyundai GLOBAL\Country folders\EUROPE\A3 Ops reports"


Sheets("Dashboard - ENG").Select
Sheets("Dashboard - ENG").Copy


Cells.Select
Range("C1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Ex_Ref = Sheets("Dashboard - Eng").Range("L1")
St = FilePath + "\Dashboard - ENG"
Ref = Format(DateAdd("m", -1, Now()), "yyyymm")
En = ".xlsx"


ChDir FilePath
ActiveWorkbook.SaveAs Filename:=St & " " & " " & Ex_Ref & " " & Ref & En

ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
"C:\Program Files (x86)\Microsoft Office\Document Themes 15\Theme Colors\Office 2007 - 2010.xml" _
)



ActiveWindow.Close





End Sub