Save Excel 2007 File as "CompanyName" + Range("E1")

Hank Isaacs
Hank Isaacs used Ask the Experts™
on
Hi,

Have a macro running in Excel 2007 that simply copies a specific worksheet to a new file and saves it.

I would like the new file to be saved using a combination of our company name "MediaSTAR PO" plus the text located in cell ("E1").

Below is the current macro with just the company name.  Need help with adding cell text.

-Thanks


Sheets("Pyramid Purchase Order").Select
    Sheets("Pyramid Purchase Order").Copy
    ChDir _
        "X:\Hank\Business\Financial, Sales & Performance Data\Sales\Weekly Purchase Orders\Pyramid"
    ActiveWorkbook.SaveAs Filename:= _
        "X:\Hank\Business\Financial, Sales & Performance Data\Sales\Weekly Purchase Orders\Pyramid\MediaSTAR_PO.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Sort").Select
    Range("K1").Select
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I think, in the macro, after saving new file, you should open the workbook and put the cell(E1) the new file name and save again.
Here you go.
Sheets("Pyramid Purchase Order").Select
    Sheets("Pyramid Purchase Order").Copy
    ChDir _
        "X:\Hank\Business\Financial, Sales & Performance Data\Sales\Weekly Purchase Orders\Pyramid"
    ActiveWorkbook.SaveAs Filename:= _
        "X:\Hank\Business\Financial, Sales & Performance Data\Sales\Weekly Purchase Orders\Pyramid\MediaSTAR_PO" & Activeworkbook.Range("E1").Value & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Sort").Select
    Range("K1").Select
End Sub

Open in new window

Commented:
Hi Trungk43:  

There are a few ways to accomplish this.
Probably the easiest is to use the concatenate function to create the desired file name in a single cell (in the attached example, cell "e2").  Then pass the value of Cell e2 to the macro.  

Drive      Directory      Filename      Version      
C      work      Filename      a      C:\work\Filename-a

The concatenate command would look like this and is located in the "e" column:
=TEXT(CONCATENATE(A2,":\",B2,"\",C2,"-",D2),)

The macro would include a line similar such as -

ActiveWorkbook.SaveAs Worksheets("sheet1").Range("e2")

Obviously you could include any of the activeworkbook.saveas parameters as necessary for your particular purpose.
Would you like for me to upload a file?

Regards,

Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial