Hey all,
I have a workbook that after updating needs to be saved without any linked formulas and into a monthly file.
Now i am in Portugal what means that when formatting text for my month it uses the Portuguese language in stead of English. Using formulas there is the [$-409] code i can use that does the job very well. Now i have tried that in a macro, but i can not get this to work. Searching the net also gives me no help so ill try here where all the guru's at
What i have:
Thanks in advance for any input.
I have a workbook that after updating needs to be saved without any linked formulas and into a monthly file.
Now i am in Portugal what means that when formatting text for my month it uses the Portuguese language in stead of English. Using formulas there is the [$-409] code i can use that does the job very well. Now i have tried that in a macro, but i can not get this to work. Searching the net also gives me no help so ill try here where all the guru's at
What i have:
Code:
Sub CopyValuesNewFile()
'Copy all external linked formulas and save them as values
Worksheets("CALCULATIONS").Columns("V").Copy
Worksheets("CALCULATIONS").Columns("V").PasteSpecial xlPasteValues
Worksheets("CALCULATIONS").Range("$D$2:$U$6").Copy
Worksheets("CALCULATIONS").Range("$D$2:$U$6").PasteSpecial xlPasteValues
'Hide helper Tabs
Worksheets("CALCULATIONS").Visible = xlSheetVeryHidden
Worksheets("Addons").Visible = xlSheetVeryHidden
Worksheets("Control").Visible = xlSheetVeryHidden
'Set new filename and date
Dim Mon As String, Ye As String, wbName As String, xWB As Workbook
wbName = "Philips_Forecast_Benelux_Draft_"
Mon = Format(CStr(Now), "[$-409]mmmm")
Ye = Format(CStr(Now), "_yyyy")
'Disable Excel warnings
Application.DisplayAlerts = False
'Goto First Sheet
Worksheets("DASHBOARD CC").Activate
Range("A1").Activate
'Save new file in right path
ActiveWorkbook.SaveAs Filename:="\\inffs223\MissionControl\MC\P.EMEA.PHIL_207\01 - Forecast & Sizing\02 - FORECAST\04 - VALIDATION\" & wbName & Mon & Ye, FileFormat:=51, CreateBackup:=False
'Enable Excel warnings
Application.DisplayAlerts = True
End Sub
Thanks in advance for any input.