Save with ref. to cell A1

[Chiefen]

I have on macro that is copying one sheet into another workbook and
would like to save it with ref. to cell A1. It has to be saved in same
folder as the original workbook regardless if I move the original
workbook to another folder.

Sub Macro1()
Sheets("sheet1").Select
Sheets("Sheet1").Copy

ActiveWorkbook.SaveAs Filename:= ????????

FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
End Sub

 

[Dave Peterson]

Maybe...

Option Explicit
Sub Macro1()
Dim myPath As String

myPath = ActiveWorkbook.Path

Sheets("Sheet1").Copy 'to a new workbook

With ActiveSheet
.Parent.SaveAs Filename:=myPath _
& "\" & .Worksheets(1).Range("a1").Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
.Parent.Close savechanges:=False
End With
End Sub


This assumes that the value in A1 can be used as a valid file name.

 

[Chiefen]

Maybe...

Option Explicit
Sub Macro1()
     Dim myPath As String

     myPath = ActiveWorkbook.Path

     Sheets("Sheet1").Copy 'to a new workbook

     With ActiveSheet
         .Parent.SaveAs Filename:=myPath _
             & "\" & .Worksheets(1).Range("a1").Value & ".xls", _
             FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
             ReadOnlyRecommended:=False, CreateBackup:=False
         .Parent.Close savechanges:=False
     End With
End Sub

This assumes that the value in A1 can be used as a valid file name.

Thanks... Don't know how you did it, but it's working...

Thanks again

Svein