Adding a sheet to a workbook

[Paul]

I have a Development Notes template that I want to add to the front of larger
models that I build.

Having saved the template as a file, I have code that is run from an icon
that should
- identify the activeworkbook
- open the template file
- copy the notes sheet into the activeworkbook
- close the template file

It keeps failing on the second of these two lines, with a 'Subscript out of
range' error

cSheet = Workbooks(cMainWin).Sheets(1).Name
Workbooks(cNotesFile).Worksheets(cNotesFile).Copy
Before:=Workbooks(cMainWin).Sheets(cSheet)

' cNotesFile is the name of the notes template
' cMainWin is the name of the activeworkbook

The first line attempts to get the name of the first sheet in the file (I
originally tried using Sheet(1) but with the same error)

This should be straightforward, but clearly I'm making it more difficult
than it should be - and making it fail at the same time.

Any help would be appreciated

 

[john]

something like following should do what you want.

Sub CopyWorksheetFromTemplate()
Dim wb1 As Excel.Workbook
Dim wb2 As Excel.Workbook
Dim TemplateFile As String
Dim FolderName As String

TemplateFile = "cNotesFile.xlt"

FolderName = "C:\Documents and Settings\Paul\Application
Data\Microsoft\Templates\"

Application.ScreenUpdating = False

Set wb1 = ActiveWorkbook 'Workbooks("cMainWin.xls")


On Error Resume Next
Set wb2 = Workbooks(TemplateFile)
On Error GoTo 0

If wb2 Is Nothing Then

Set wb2 = Workbooks.Open(FolderName & TemplateFile, Editable:=True)

Else

Set wb2 = Workbooks(TemplateFile)

End If

wb2.Worksheets(1).Copy Before:=wb1.Sheets(1)

wb2.Close False

Application.ScreenUpdating = True

End Sub