Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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. "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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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" wrote: 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. "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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
See Dave's posting.
"SteveDB1" wrote: 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" wrote: 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. "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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
And it only took a few times to get it right <vbg!
SteveDB1 wrote: 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" wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sheet name change
<vvvbsbg
scary, ain't it..... ;-) thanks again....! "Dave Peterson" wrote: And it only took a few times to get it right <vbg! SteveDB1 wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change formula in shared sheet without loss of change histo | Excel Worksheet Functions | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming | |||
How to record a sheet change showing row column sheet name and date? | Excel Programming |