HOW DO I AUTO NAME A SAVED FILE FROM A SPEC. BLOCK IN EXCEL?

[bbrinker]

I AM LOOKING TO AUTO NAME A FILE. i WOULD LIKE TO BE ABLE TO PICK AND CHOOSE
WHAT BLOCK THE FILE NAME COMES FROM INSTEAD OF USING BLOCK "A1" I WOULD LIKE
TO CHOOSE FOR EXAMPLE BLOCK "K6". THANKS.

 

[bbrinker]

Sorry, Didn't imply to shout...was drawing in AutoCAD and plots require all
caps. when I spoke of "block" i was refering to cells. I am using the
default for saving a file but I would like to choose a cell location for
Excel to auto grap and use what ever values are in that cell as its "save as"
name. Thx Again.

 

[Gord Dibben]

Sub Make_New_Book()
Dim w As Worksheet
Application.DisplayAlerts = False
Set srng = Application.InputBox(prompt:= _
"Select Desired Cell", Type:=8)
ActiveWorkbook.SaveAs Filename:="C:\testing\" _
& srng.Value & ".xls"
Application.DisplayAlerts = True
End Sub

No error checking for blank cells or illegal characters.


Gord Dibben MS Excel MVP

 

[bbrinker]

Thanks for the reply, put I am sum-what familiar with excel but a dummie at
most...can you please give me step by step instructions one how to apply the
lines of code...thanks again

 

[Gord Dibben]

With your workbook open hit Alt + F11 to go to Visual Basic Editor.

CTRL + r to open Project Explorer.

Select your workbook/project and right-click>insert>module.

Paste the macro into that module.

Alt + q to return to the Excel window.

Save the file as is then Tools>Macro>Macros.

Select the macro name and "Run" to save the workbook with the name from the
cell you chose.

You now have two identical workbooks with different names, the original and
the re-named one.


Gord

 

[bbrinker]

Thanks again...I will give it a try

With your workbook open hit Alt + F11 to go to Visual Basic Editor.

CTRL + r to open Project Explorer.

Select your workbook/project and right-click>insert>module.

Paste the macro into that module.

Alt + q to return to the Excel window.

Save the file as is then Tools>Macro>Macros.

Select the macro name and "Run" to save the workbook with the name from the
cell you chose.

You now have two identical workbooks with different names, the original and
the re-named one.


Gord

 

[bbrinker]

I tried entering the lines of code but couldn't get it to work...does it
matter that the workbook was designed in excel 2003 and auto opens in 2007?
any sugg. ?

 

[Gord Dibben]

I don't have any idea.

I don't run 2007 so will have to leave your question to someone who can
replicate your situation.


Gord