Make save as when sheet name is based on cell value.

[Harshil Mehta]

I have total 6 sheets in my excel workbook from which I want to make a save as of first 5 sheets whose sheet names are based on cell value of sheet(6) E17,F17,G17,H17.I17 [Sheet Name is DASHBOARD]. So if the value in the cell changes the names of the sheets also changes. Please find below the code and would be thankful if anyone could help me.

Sub Make_SaveAs()

Worksheets(Array(Sheets(1), Sheets(2), Sheets(3), Sheets(4), Sheets(5))).Copy
With ActiveWorkbook
.SaveAs Filename:=Environ("USERPROFILE") & "\Desktop\" & Format(Now(), "YYYYMMDD HHMM") & " " & ThisWorkbook.Sheets(3).Range("C2") & " Design Format.xlsx", FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With



MsgBox "File Saved on Deskstop"

End Sub

 

[Harshil Mehta]

ThisWorkbook.Sheets(6).Range("C2")