Saving a Workbook using a variable to name it

Joined
Nov 29, 2018
Messages
11
Hi
I copy a range from one workbook, then creating a new book to save this data in.
I’m trying to save this newly created workbook to a directory and name the workbook as a value of a range in that newly created workbook. That is range(“G1”) which is a date in Excel numeric (43735).
My code for this is: -
Sub NewWorkbook()
Windows("Trader.xlsm").Activate
Range("B22:R22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Personal.xlsm").Activate
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
ActiveWorkbook.SaveAs Filename:="C:\Users\USER\New TestnTrade\NewWorkbook.xlsm", _

ActiveWindow.Close
End Sub
Trader.xlsm is a workbook that I copy a range from and paste that range to Newworkbook.
Instead of saving the new workbook as Newworkbook, I would like to save it as the value that appears in Range (“G1”) of this book (a numeric date).
Chris
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
something like
Dim NewName as String
NewName = Range("G1")

ActiveWorkbook.SaveAs Filename:="C:\Users\USER\New TestnTrade" & NewName & ".xlsm"
 
Upvote 0
If in G1 you have a date and want to convert it to an excel number, then try this:

Code:
Sub NewWorkbook()
[COLOR=#ff0000]  Dim sName As String[/COLOR]
  Windows("Trader.xlsm").Activate
[COLOR=#ff0000]  sName = Evaluate("=""""&G1")[/COLOR]
  Range("B22:R22").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  'Windows("Personal.xlsm").Activate
  Workbooks.Add
  Selection.PasteSpecial Paste:=xlPasteValues
  ActiveWorkbook.SaveAs Filename:="C:\Users\USER\New TestnTrade[B][COLOR=#ff0000]\[/COLOR][/B]" & [COLOR=#ff0000]sName [/COLOR]& ".xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled
  ActiveWindow.Close
End Sub
 
Upvote 0
Hi mr. mole999,
Thanks for replying. I tried the code as below and got this error: -
:Run-type error 1004
This extension cannot be used with this selection file type. Change the file extension in the File name text box or select a different file type by changing the Save as type
What could that mean?

code: -
Sub NewSheet2()

Windows("Trader.xlsm").Activate
Range("B22:R22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Personal.xlsm").Activate
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim NewName As String
NewName = Range("G1")

ActiveWorkbook.SaveAs Filename:="C:\Users\USER\New TestnTrade" & NewName & " .xlsm"

End Sub
 
Upvote 0
Hi Dante

Thanks for replying. I tried the code as below and got this error: -


Run type error 1004. Method ‘SaveAs ‘of object ‘_Workbook’ failed
What could that mean?


code: -
Sub NewWorkbook()
Dim sName As String
Windows("Trader.xlsm").Activate
sName = Evaluate("=""""&G1")
Range("B22:R22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Windows("Personal.xlsm").Activate
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.SaveAs Filename:="C:\Users\USER\New TestnTrade\" & sName & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWindow.Close

End Sub
 
Upvote 0
Hi Dante

Thanks for replying. I tried the code as below and got this error: -
Run type error 1004. Method ‘SaveAs ‘of object ‘_Workbook’ failed
What could that mean?

What does the error message say?
What version of Excel do you have?
 
Upvote 0
Range G1 neds to be nominated which workbook you are using, also i left a backslash out
 
Upvote 0
What does the error message say?
Run type error 1004. Method ‘SaveAs ‘of object ‘_Workbook’ failed

What version of Excel do you have?
Windows 8.1 Pro Office 365 Supscription
Chris

 
Upvote 0
If your original macro has no problems then my code should not have problems.


Did you modify some of the macro?

You can put your full macro again.
 
Upvote 0
I tried this: -

Sub NewSheet2()


Windows("Trader.xlsm").Activate
Range("B22:R22").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Personal.xlsm").Activate
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim NewName As String
NewName = ActiveWorkbook.Range("G1")


ActiveWorkbook.SaveAs Filename:="C:\Users\USER\New TestnTrade" & NewName & " .xlsm"


End Sub

But I got
Error: -Run-time error Object doesn't support this property or method
Chris.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top