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

B

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

David Biddulph

Firstly, please get your caps lock key repaired so that you can stop
SHOUTING.

Secondly, what do you mean by "block" in this context? Do you mean "cell"?

Thirdly, if you have some existing method of auto naming the file which is
using A1, it may be useful if you explain what method you are using, then we
might be able to tell you how to change it.
 
B

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

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
 
B

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
 
G

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
 
B

bbrinker

Thanks again...I will give it a try

Gord Dibben said:
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
Click to expand...
 
B

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. ?
 
G

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
 
Top