Excel 2007 'File Save As' Macro

miasha used Ask the Experts™
I need assistance in writing macro code that will do the following:

Save the contents of a print range and automatically give it a file name based on a specific cell name.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you mean save the contents of a preset print range (on a single sheet?) to a new file?


Basically, I already have written that queries info in my workbook, then based on criteria I set, it copies the results into a specific area on my worksheet.  This result is what I will print out.  However, I will query multiple times for different info, so the same print range will contain different info each time.  So - what I want to do is have the macro save the print range each time and automatically save the file with the name that is in a specific cell.  For example, I want the macro to use cell 'U1' as the name of the file (the info in cell 'U1' will change each time I do a query - which is why I want the macro to automatically assign the file name using the contents of cel 'U1.'  So basically, I want the macro to:

1) Save the present print range to a new file
2) Automatically assign it the file name of the cell contents in 'U1'.
Sorry, I'd missed your reply
Pls try this

Sub MakeEM()
Dim X(), fName As String
fName = [u1].Value
X = Range("Print_Area")
Range("Print_Area") = X
If Not fName = vbNullString Then ActiveWorkbook.SaveAs ("C:\temp\" & fName & ".xls")
End Sub

Open in new window

My solution is valid.

I suggest Excel ZA review if you want a second opinion



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