SaveCopyAs Cell Ref.

[Wev]

I know this is really easy (for some!) but i am stuck,
I want to save a copy of a workbook using a cell ref (A1) as the file name.
The copies will be weeks of the year.
If possible i would like to save and close the original as well.


TIA
Wev

 

[Wev]

Sorry, forgot to include that i want to save the copy in a different folder
to the original.

 

[Geoff]

Wev, try putting the code below into the book and then
running a macro (perhaps using a Control Button for ease).
Change the sheet name to your sheet, and in Cell A1. This
will place the file in the current folder (ie the one
which is visable in the file - Open dialog box).

Sub NewFileName()
' Saves the file as the name on Sheet 1 Cell A1 and
closes the file

Dim RngFileName As String

RngFileName = ThisWorkbook.Worksheets("Sheet1").Range
("A1").Value
' Sets the names of the formulas

ActiveWorkbook.SaveAs FileName:=RngFileName, _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub

Regards

 

[Wev]

Thats a great help Geoff,
Is it possible to save the new file to a different location?
This way i can keep them separate from the main workbook.

 

[Geoff]

Wev, try this. It uses the file name in cell A1, and a
reference to the directory location in A2. Hope this helps

Sub MoveSheetAndSaveFile()
' Moves the Sheet to another file and gives it a name
based on Cells A1 & A2

Dim RngFileName As String
Dim RngDirectoryLocation As String

RngFileName = ThisWorkbook.Worksheets("Sheet1").Range
("A1").Value
RngDirectoryLocation = ThisWorkbook.Worksheets
("Sheet1").Range("A2")

Sheets("Sheet1").Move
ActiveWorkbook.SaveAs FileName:=CStr
(RngDirectoryLocation) + CStr(RngFileName), _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
End Sub

 

[Wev]

Works fine Geoff,
I did change the line
"Sheets("Sheet1").Move" to
"Sheets("Sheet1").Copy"
so that it copied the page only, but apart from that its great.
Thanks for your help

Wev