sheet name change

[SteveDB1]

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

 

[JLGWhiz]

I think this:

Sheets(Sheets.Count).Name = NewNm

Should be this:

Workbooks(WkBkName1).Sheets(Sheets.Count) = NewNm

to tie it down to exactly where the sheet is. The last workbook that was
activated was the template workbook, it is counting the sheets in that
workbook without otherwise specifying.

 

[Dave Peterson]

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]

Hi JLG,
I tried your recommendation. It did not work. I also tried
workbooks(wkbkname1).activate, and that did not work either.
I then tried

sheets(sheets.count + 1).name = newnm

That threw an out of range error.

From what I've been able to identify, it renames the last sheet-- in this
case it's hidden-- in my wkbkname1 and leaves the new sheet's name alone.

I did some more digging, and tried one more thing.
I tried adding '+ 1' to ShtCnt = ActiveWorkbook.Sheets.Count

I.e., ShtCnt = ActiveWorkbook.Sheets.Count + 1

That too threw and out of range error.

What I noticed was that in this case 3 of the total 10 worksheets in this
file were hidden. The hidden worksheet that was being renamed was the last in
order from first to last of the 10 sheet.

For the sake of the discussion I moved the placement of the 3 hidden
sheets-- after they were unhidden-- to the front of the file.
I then ran the macro again, and all operated exactly as intended.
I.e., it named the correct worksheet the correct name, and left the last
existing worksheet's name alone.

So, how is it-- without having to move hidden worksheets to the front of the
list-- that I can get this to correctly name the correct worksheet, without
affecting existing worksheets' names?

Thank you for your helps.
Best,
SteveB.

 

[JLGWhiz]

See Dave's posting.

Hi JLG,
I tried your recommendation. It did not work. I also tried
workbooks(wkbkname1).activate, and that did not work either.
I then tried

sheets(sheets.count + 1).name = newnm

That threw an out of range error.

From what I've been able to identify, it renames the last sheet-- in this
case it's hidden-- in my wkbkname1 and leaves the new sheet's name alone.

I did some more digging, and tried one more thing.
I tried adding '+ 1' to ShtCnt = ActiveWorkbook.Sheets.Count

I.e., ShtCnt = ActiveWorkbook.Sheets.Count + 1

That too threw and out of range error.

What I noticed was that in this case 3 of the total 10 worksheets in this
file were hidden. The hidden worksheet that was being renamed was the last in
order from first to last of the 10 sheet.

For the sake of the discussion I moved the placement of the 3 hidden
sheets-- after they were unhidden-- to the front of the file.
I then ran the macro again, and all operated exactly as intended.
I.e., it named the correct worksheet the correct name, and left the last
existing worksheet's name alone.

So, how is it-- without having to move hidden worksheets to the front of the
list-- that I can get this to correctly name the correct worksheet, without
affecting existing worksheets' names?

Thank you for your helps.
Best,
SteveB.

 

[SteveDB1]

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]

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

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.

 

[SteveDB1]

Thanks Dave.
I tested it-- I moved the hidden sheet that was being changed, back to the
end, re-hid it, and checked your code.

It worked as desired.

The correct sheet was named correctly, and the hidden sheet was ignored.

Thanks again for your help.... another satisfied customer. :-D

Best,
SteveB.

 

[Dave Peterson]

And it only took a few times to get it right <vbg>!

Thanks Dave.
I tested it-- I moved the hidden sheet that was being changed, back to the
end, re-hid it, and checked your code.

It worked as desired.

The correct sheet was named correctly, and the hidden sheet was ignored.

Thanks again for your help.... another satisfied customer. :-D

Best,
SteveB.

 

[SteveDB1]

<vvvbsbg>
scary, ain't it..... ;-)
thanks again....!