Microsoft Office Forums - View Single Post

BrianT · 05-11-2019, 10:39 AM

Hi

I have adapted this macro from various threads, however, it runs, but does not save as expected, only one sheet saves, not always the same sheet, and not always the correct filename

In short, 12 sheets in one workbook, run macro to move and save in folder on desktop, Filename is from cells A2, and C2 and L2 so one save file should be Acumen week 47 Week 48

A2 - Acumen, C2 Week 47, L2 Week 48

Any help, direction, would be appreciated

Regards

Brian

Code:

Dim part1 As String
Dim part2 As String
Dim part3 As String
'setting out the cells
part1 = Range("A2").Value
part2 = Range("C2").Value
part3 = Range("L2").Value


Dim strUser As String
    strUser = Environ("UserName")
    
Application.DisplayAlerts = False
    

Sheets("Acumen").Select
Sheets("Acumen").Move
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close



Sheets("Carlson").Select
Sheets("Carlson").Move
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close



Sheets("Gefco").Select
Sheets("Gefco").Move
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" & strUser & "\Desktop\Forecast\" & part1 & " " & part2 & " " & part3 & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

Etc etc