Open a new workbook with a specific name

[Guest]

Hi. Im seeking some help here.

Im trying to open via a Macro a new workbook, do some calcultations and then
save this new workbook with a specific name. Problem is that this name is
variable (It depends in this case of the Date brought from another workbook).

How can i make my macro recogniza this date form the first workbook, and
save the second one with this exact variable?

This is what i've got so far:

Sub exportmonth()

Date = Range("b1").Value 'the date taken from the first workbook
Cells.Select
Selection.Copy
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="H:\excel\test.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


End Sub


In this case the new workbook is saved as text.xls but thats no use for me

Any kinda help appreciated
Thanx

 

[Guest]

Try this
Sub exportmonth()
Const saveToPath = "H:\excel\"
Dim myWorkbookName As String

myWorkbookName = Format(Range("B1").Value, "[$-409]mmddyy;@") & ".xls" 'the
date taken from the first workbook
Cells.Select
Selection.Copy
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=saveToPath & myWorkbookName,
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


End Sub

 

[Dave Peterson]

It looks like you're just saving a new workbook--your code doesn't do any
pasting????

Option Explicit
Sub exportmonth2()

Dim myDate as Date
dim ActSheet as worksheet
Dim Wkbk as workbook

set actsheet = activesheet
myDate = actsheet.range("b1").value

set wkbk = workbooks.add(1) 'single sheet

'copy to the new worksheet
actsheet.cells.copy _
destination:=wkbk.worksheets(1).range("a1")

wkbk.saveas filename:="H:\excel\" & format(mydate,"yyyy-mm-dd") & ".xls", _
FileFormat:=xlNormal

'close that new workbook?
wkbk.close savechanges:=false

End Sub

You could copy the whole sheet to a new workbook, too--instead of copying the
cells and pasting.

Option Explicit
Sub exportmonth2a()

Dim myDate as Date
dim ActSheet as worksheet

set actsheet = activesheet
myDate = actsheet.range("b1").value

actsheet.copy 'creates a new workbook

activeworkbook.saveas filename:="H:\excel\" _
& format(mydate,"yyyy-mm-dd") & ".xls", FileFormat:=xlNormal

'close that new workbook?
activeworkbook.close savechanges:=false

End Sub

 

[Guest]

Thats exaclty what i needed, to copy exactly the same file (and eliminating
some parts but that easy).

Youv been of great help Dave Thanx