Oops. I dropped the line that added the extension:

This section changes:

If Len(Trim(WkBkName)) = 0 Then
Exit Sub
End If

Set wkbk = Nothing

to:

If Len(Trim(WkBkName)) = 0 Then
Exit Sub
End If

wkbkname = wkbkname & ".xlsx" '<-- added

Set wkbk = Nothing



SteveDB1 wrote:

Hi Dave,
Thanks for the reply.

Yours asked to choose an open workbook, even though the workbook was in fact
open. I then thought that it was being "picky" on asking for case sensitive
letters, and use them. That too resulted in asking for an open workbook.

Any more ideas?

Again-- thank you.
Best,
SteveB.

"Dave Peterson" wrote:

This compiled for me, but I didn't test it.

Option Explicit
Sub MkNewABSTWkSht() '(control As IRibbonControl)

Dim WkBkName As String
Dim wkbk As Workbook
Dim NewWks As Worksheet
Dim TmpltWB As Workbook
Dim NewNm As String

Application.ScreenUpdating = False

Set TmpltWB = Workbooks.Open _
(Filename:="C:\Documents and Settings\sbuckley\" _
& "Application Data\Microsoft\Templates\TR Claim Book.xltx", _
Editable:=True)

WkBkName = InputBox _
(prompt:="enter workbook name of where to copy worksheet", _
Title:="Copy worksheet to existing workbook")

If Len(Trim(WkBkName)) = 0 Then
Exit Sub
End If

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks(WkBkName)
On Error GoTo 0
If wkbk Is Nothing Then
MsgBox "Please enter a workbook name that's open"
Exit Sub
End If

TmpltWB.Sheets("Tab # ").Copy _
after:=wkbk.Sheets(wkbk.Sheets.Count)

Set NewWks = ActiveSheet
'or
'Set NewWks = wkbk.Sheets(wkbk.Sheets.Count)

NewNm = InputBox _
(prompt:="What is the sheet number you " _
& "want to call this worksheet?", _
Title:="New Abstract Worksheet Name")

On Error Resume Next
NewWks.Name = NewNm
If Err.Number < 0 Then
MsgBox "Invalid name!" & vbLf _
& "Please rename: " & vbLf _
& NewWks.Name & vbLf & "manually!"
Err.Clear
End If

TmpltWB.Close SaveChanges:=False

Application.ScreenUpdating = True

End Sub


SteveDB1 wrote:

Hi all.
I've got a macro that copies a worksheet from a template workbook, and
provides a name of my choosing.
More frequently of late, the macro is changing the name of an existing
hidden worksheet to match the name that I choose, instead of the newly added
worksheet. I know that because I'd check the name to ensure it didn't exist
beforehand.

Below is the full macro.
-------------------------------------------------------

Sub MkNewABSTWkSht(control As IRibbonControl)

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

Workbooks.Application.ScreenUpdating = False

Workbooks.Open Filename:= _
"C:\Documents and Settings\sbuckley\Application Data\Microsoft\Templates\TR
Claim Book.xltx" _
, 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)

NewNm = InputBox(prompt:="What is the sheet number you want to call this
worksheet?", Title:="New Abstract Worksheet Name")

Sheets(Sheets.Count).Name = NewNm


TmpltWB.Activate

TmpltWB.Close SaveChanges:=False
'make some additions to this which will copy the headers of the last
abstract worksheet.
'the goal being to copy the Decreed owner's name and the successor if any,
as well as the claim #
' and the decree book page #.


'When all done...
Set TmpltWB = Nothing
End Sub

--------------------------------------------

How can I fix this from continuing to happen?
Thank you.

--

Dave Peterson


--

Dave Peterson