After Workbook.Add

  • Thread starter Thread starter chrisnsmith
  • Start date Start date
C

chrisnsmith

I use the following macro I created with the macro recorder to add a new
workbook
without vb code for emailing. What I need to know is how to give a name to
this workbook after it is created. Help would be appreciated.

Workbooks.Add

Windows("Form 200").Activate
Sheets(Array("Cus Futures", "House Futures", "Cus Calls", "Cus Puts", _
"House Options", "Cus Indexes")).Select
Sheets("Cus Futures").Activate
Sheets(Array("Cus Futures", "House Futures", "Cus Calls", "Cus Puts", _
"House Options", "Cus Indexes")).Copy Before:=Workbooks("Book2").Sheets(1)
 
chrisnsmith -
to add to what eksh said ... put the line of code he provided right after
your
Workbooks.Add statement before coming back to the primary workbook. You
will also need to change the name of the workbook from "Book2" down in the
final statement you posted here. Might consider using a variable to build up
a new name, this code would take the current workbook's name and add a date
stamp to it to use as the filename. Code could look something like this:

Dim newFileName As String

newFileName = Left(ThisWorkbook.Name, _
InStrRev(ThisWorkbook.Name, ".") - 1)
newFileName = newFileName & "_" & _
Format(Year(Now()), "0000") & "_" & _
Format(Month(Now()), "00") & "_" & _
Format(Day(Now()), "00") & ".xls"

Workbooks.Add
ActiveWorkbook.SaveAs newFileName

Windows("Form 200").Activate
Sheets(Array("Cus Futures", "House Futures", "Cus Calls", "Cus Puts", _
"House Options", "Cus Indexes")).Select
Sheets("Cus Futures").Activate
Sheets(Array("Cus Futures", "House Futures", "Cus Calls", "Cus Puts", _
"House Options", "Cus Indexes")).Copy _
Before:=Workbooks(newFileName).Sheets(1)
 
Back
Top