Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
After Workbook.Add
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 Befo=Workbooks("Book2").Sheets(1) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
After Workbook.Add
you need to use save as method i.e.
ActiveWorkbook.SaveAs ("Filename") "chrisnsmith" wrote: 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 Befo=Workbooks("Book2").Sheets(1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
After Workbook.Add
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 _ Befo=Workbooks(newFileName).Sheets(1) "eksh" wrote: you need to use save as method i.e. ActiveWorkbook.SaveAs ("Filename") "chrisnsmith" wrote: 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 Befo=Workbooks("Book2").Sheets(1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a chart to large workbook brings workbook activity to a hal | Excel Discussion (Misc queries) | |||
Macro to copy an image (or picture) from one workbook to a new sheetin another workbook | Excel Worksheet Functions | |||
Excel workbook is corrupted, workbook automatically saves on start | Excel Discussion (Misc queries) | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Multiple workbook user's with Master workbook - all password protected | Excel Discussion (Misc queries) |