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
--
jb
"Paul" wrote:
> 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
>
> --
> If the post is helpful, please consider donating something to an animal
> charity on my behalf .......... and click Yes
|
Similar Threads
