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.

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.
--
David Biddulph

"bbrinker" wrote in message
...
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.

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.

"bbrinker" wrote:

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.

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

On Tue, 16 Dec 2008 06:53:03 -0800, bbrinker
wrote:

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.

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" wrote:

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

On Tue, 16 Dec 2008 06:53:03 -0800, bbrinker
wrote:

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.

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

Paste the macro into that module.

Alt + q to return to the Excel window.

Save the file as is then ToolsMacroMacros.

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



On Tue, 16 Dec 2008 11:53:04 -0800, bbrinker
wrote:

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" wrote:

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

On Tue, 16 Dec 2008 06:53:03 -0800, bbrinker
wrote:

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.

Thanks again...I will give it a try

"Gord Dibben" wrote:

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

Paste the macro into that module.

Alt + q to return to the Excel window.

Save the file as is then ToolsMacroMacros.

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



On Tue, 16 Dec 2008 11:53:04 -0800, bbrinker
wrote:

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" wrote:

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

On Tue, 16 Dec 2008 06:53:03 -0800, bbrinker
wrote:

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.

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

"bbrinker" wrote:

Thanks again...I will give it a try

"Gord Dibben" wrote:

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

Paste the macro into that module.

Alt + q to return to the Excel window.

Save the file as is then ToolsMacroMacros.

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



On Tue, 16 Dec 2008 11:53:04 -0800, bbrinker
wrote:

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" wrote:

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

On Tue, 16 Dec 2008 06:53:03 -0800, bbrinker
wrote:

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.

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

On Tue, 16 Dec 2008 12:46:09 -0800, bbrinker
wrote:

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

"bbrinker" wrote:

Thanks again...I will give it a try

"Gord Dibben" wrote:

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

Paste the macro into that module.

Alt + q to return to the Excel window.

Save the file as is then ToolsMacroMacros.

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



On Tue, 16 Dec 2008 11:53:04 -0800, bbrinker
wrote:

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" wrote:

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

On Tue, 16 Dec 2008 06:53:03 -0800, bbrinker
wrote:

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.