Save with ref. to cell A1


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
 
Ad

Advertisements

D

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.
 
Ad

Advertisements

C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simplify save code 11
Create CSV 3
Saving a workbook to an iKnow portal 2
Code to share a file 3
Macro to save a workbook 1
Hiding an Excel file using VBA 1
Recording a 'Save As...' 4
Bypass Auto_Open 13

Top