Save with ref. to cell A1

  • Thread starter Thread starter Chiefen
  • Start date Start date
C

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
 
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.
 
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.
Click to expand...



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

Thanks again

Svein
 
Back
Top