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

[Greg]

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

 

[Chip Pearson]

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

 

[Dick Kusleika]

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"

 

[Greg]

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

 

[Greg]

Chip,

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

Thanks.

 

[Greg]

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

 

[Dick Kusleika]

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.