msg Box Value to be Saved File Name ??

[Corey]

It the below msg box i want the value that is entered INTO the msg box to
NAME the file to Save. (eg. File 10)

Sub Macro20()
Range("A4").Select
Selection.ClearContents

res = InputBox("The MAXIMUM number of Sheets have been reached,
This is a NEW COPY of the WorkBook.", "Company Name ....")

If res = "" Then Exit Sub
ActiveWindow.DisplayWorkbookTabs = False
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End With

End Sub



What step needs to added to madify this so it will name it this?

Corey....

 

[NickHK]

Corey,
I guess you have to answer the user a question:
res = InputBox("What is the name of the file ?", "Company Name ....")
ThisWorkbook.SaveAs res
- with error handling added
But you also pop up the SaveAs dialog, that's its purpose:
Application.dialogs(xlDialogsaveAs).show

NickHK

 

[Leith Ross]

Hello Corey,

Windows will only rename a closed file. Once you have done that you can
use the 'Name... As...' statement to rename the workbook. The macro
would have to be run from the original workbook since you need to close
the copy.

Name As Statement Example:
Name <old path name> As <new path name>

Path name can also include the Drive and Folder(s).
The New path name doesn't have to be on the same drive or in the same
directory as the old path name.

Sincerely,
Leith Ross

 

[Corey]

Thanks for the reply Nick.
Your comments clear up my untrained mind about an Input and a msg boxes.

One thing i found is that the below code SavesAs. the current file opened
and NOT the file that is is copying.

What can i change in the: ThisWorkbook.SaveAs res string
to to refer to the NEW file instead?

Corey....

 

[Corey]

Thanks for the reply Leith.

My post above your post explains what i am after a bit further.

I have just realised also that, IF i click the CANCEL in the Input box, i
get an error AND the NEW WorkBook is STILL created.

res = InputBox("The MAXIMUM No. of Records is reached, NAME NEW File AS ? ",
"Company Name...")
ThisWorkbook.SaveAs res
If res = "" Then Exit Sub <=============
Does this not stop the macro if CANCEL or nothing is entered ?
Application.Dialogs(xlDialogSaveAs).Show <============= Want this to SAVE
NEW FILE NOT old file....
ActiveWindow.DisplayWorkbookTabs = True
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = False
End With

End Sub


Corey.....

 

[Chip Pearson]

The order of your commands is wrong. Rather than

res = InputBox("The MAXIMUM No. of Records is reached, NAME NEW
File AS ? ", _
"Company Name...")
ThisWorkbook.SaveAs res
If res = "" Then Exit Sub

you need
res = InputBox("The MAXIMUM No. of Records is reached, NAME NEW
File AS ? ", _
"Company Name...")
If res = "" Then Exit Sub
ThisWorkbook.SaveAs res


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com