Saving a Workbook using a variable to name it

[christophermartin795]

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

 

[mole999]

something like
Dim NewName as String
NewName = Range("G1")

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

 

[DanteAmor]

If in G1 you have a date and want to convert it to an excel number, then try this:

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

 

[christophermartin795]

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
​

 

[christophermartin795]

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

 

[DanteAmor]

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?

 

[mole999]

Range G1 neds to be nominated which workbook you are using, also i left a backslash out

 

[christophermartin795]

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

 

[DanteAmor]

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.

 

[christophermartin795]

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.