Easy. We already have an object variable (TmpltWB) set to the template
workbook. Add

TmpltWB.Close SaveChanges:=False

before freeing the object variable with

Set TmpltWB = Nothing

Hutch

"SteveDB1" wrote:

Tom,
It works.
Thank you.
One last question.
How would I close the template workbook in VBA?
Again-- thank you.


"Tom Hutchins" wrote:

I'm getting the error now on the .Copy statement, not the .Activate
statement. I think Excel is returning the number of sheets in the workbook
you opened from your template, not the number of sheets in the destination
workbook. Try this:

Dim WkBkName As String, WkBkName1 As String
Dim ShtCnt As Integer, TmpltWB As Workbook

Workbooks.Open Filename:= _
"C:\Documents and Settings\UserName\Application Data\Microsoft\Templates\TR
Claim Book.xlt" _
, Editable:=True
Set TmpltWB = ActiveWorkbook

WkBkName = InputBox(Prompt:="enter workbook name of where to copy
worksheet", Title:="Copy worksheet to existing workbook")
If Len(WkBkName) = 0 Then Exit Sub
WkBkName1 = WkBkName & ".xlsx"
Workbooks(WkBkName1).Activate
ShtCnt = ActiveWorkbook.Sheets.Count
TmpltWB.Activate
Sheets("Tab # ").Select
Sheets("Tab # ").Copy After:=Workbooks(WkBkName1).Sheets(ShtCnt)

'When all done...
Set TmpltWB = Nothing

Hutch

"SteveDB1" wrote:

Ok....
I removed the Workbooks.open statement.
I also tried both WkBkName, and WkBkName1.
Both give the same error as before. "subscript out of range"

When you say- "if the WkBkName file hasn't been saved yet, it won't work" --
you're talking about an initial saveAs, correct?
The file already is saved with a file name. The reason that I've done this
is because we have to update these files on a regular basis, and so we're
adding a worksheet for each data package we're processing, and then modifying
some data on other worksheets.
The worksheet that I'm adding is from a template file, and I just want to
pull a copy over from a formatted template with all of the raw formats, sheet
layouts, etc...... on it.
I'd initially tried Workbooks(WkBkName).select but got the "subscript out of
range" error with that too.