Adding Sheets with VBA??

Posted by RoB on November 16, 2001 4:21 PM

Hi again,

I have code that adds a blank sheet to a workbook, but i had a few questions. I would like to add the new sheet one space before the last sheet. How would I do this? Also, I would like to know if it is possible to add, instead of a blank sheet, add a template that i have in excel using vba...im sure there is, can someone tell me how?

Thanks!

Posted by jaime on November 16, 2001 6:44 PM

Hi,
This examples creates a new sheet just before the last. It also formats it A little by dating it and giving it a title.

Sub newsheet()
Dim newsheet As Worksheet

Set wks = Worksheets.Add(after:=Worksheets(Worksheets.Count - 1))
wks.Name = "Sheet" & Worksheets.Count + 1
With wks
.Cells(1, 1) = "TTL Corp."
.Cells.Font.Bold = True
.Cells(1, 6) = Date
End With
End Sub

Posted by RoB on November 16, 2001 10:36 PM

This helps, but....

I was still wondering if i could add a specific template worksheet?

Thanks!

Posted by RoB on November 17, 2001 10:17 AM

Ah, i found it

Sheets.Add Type:= _
"C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates\book 1.xls"