S
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
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