After Workbook.Add

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)
 
J

JLatham

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top