Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Hi,

Can someone help me with some compact code that will:

- create a new workbook
- Name the workbook based on a 5 letter variable + month & year from a
variable. Ex. ABCDE 05-2005.xls
- Add 4 Sheets
- Name each sheet

I am working on copying data from a tool to be sent to users. I have
the copy and e-mail code working right, just need help with setting up
the blank workbook.

Much thanks,

Greg

Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Try something like the following:

Dim WB As Workbook
Dim SaveSINW As Integer
Application.ScreenUpdating = False
SaveSINW = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 4
Set WB = Workbooks.Add
Application.SheetsInNewWorkbook = SaveSINW
WB.SaveAs "ABCDF" & Format(Now, "mm-yyyy") & ".xls"
WB.Sheets(1).Name = "First Name "
WB.Sheets(2).Name = "Second Name "
WB.Sheets(3).Name = "Third Name "
WB.Sheets(4).Name = "Fourth Name "
Application.ScreenUpdating = True



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Greg" wrote in message
oups.com...
Hi,

Can someone help me with some compact code that will:

- create a new workbook
- Name the workbook based on a 5 letter variable + month & year
from a
variable. Ex. ABCDE 05-2005.xls
- Add 4 Sheets
- Name each sheet

I am working on copying data from a tool to be sent to users.
I have
the copy and e-mail code working right, just need help with
setting up
the blank workbook.

Much thanks,

Greg

Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Greg

Create a template with four sheets named how you want. Name it MyTemplate.
Then:

Dim wb as Workbook
Dim sFiveLetter as String
Dim dVariable as Date

sFiveLetter = "ABCDE"
dVariable = #05/01/2005#

Set wb = Workbooks.Add("MyTemplate.xlt")
wb.SaveAs sFiveLetter & " " & Format(dVariable, "mm-yyyy") & ".xls"


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Greg wrote:
Hi,

Can someone help me with some compact code that will:

- create a new workbook
- Name the workbook based on a 5 letter variable + month & year from a
variable. Ex. ABCDE 05-2005.xls
- Add 4 Sheets
- Name each sheet

I am working on copying data from a tool to be sent to users. I have
the copy and e-mail code working right, just need help with setting up
the blank workbook.

Much thanks,

Greg

Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Thanks guys,

Dick,
Your solution is nice and simple, but the end user will be utilizing
the code. I can't send a templet to all of them.

Chip,
Two questions:

1 - I have the date saved in a cell on a specific worksheet, can you
suggest a siple way to reference within your line that does the saveas?

2 - I don't want to leave a trace on the hard drive. Does the save as
put the file in the default directory set in the users excel options?
How would I delete after my send via e-mail code runs.

MUCH MUCH Thanks for the help,

Greg

Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Chip,

I have played around with the code and did get question number 1
answered.

Thanks.

Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Ok - next time I will play with the code before asking follow up
questions.

I found that setting a variable to the new workbook name and the
running "Kill (name)" seems to work.

Are there any issues with that command and format?

Cheers,

Greg

Create New Workbook - Name book - 4 Sheets - Name Sheets
 

Greg wrote:
Ok - next time I will play with the code before asking follow up
questions.

I found that setting a variable to the new workbook name and the
running "Kill (name)" seems to work.

Are there any issues with that command and format?

It's super dangerous if you don't know what you're doing, because it really
deletes instead of sending to the recycle bin. It sounds like you know what
you're doing, so you should have no problem using it.


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com