S
SteveDB1
Morning all.
I'm not sure how to describe this, so please bear with me.
I have a macro that calls to a template workbook, to copy a specific
worksheet out of it, and place it in an open workbook.
Presently, it calls for an input to give the name of the open-destination
workbook.
I'd like to modify this macro so that it no longer asks for the input of the
destination workbook's name.
The goal is to reduce the inadvertant mistakes of mis-spelling the name of
the destination workbook. For short file names this is not an issue, but for
longer file names this can become a problem.
Here is the code for the existing macro.
-----------------------------------------------------------------------------
Sub CopyPg5Sht() '(Optional Control As IRibbonControl)
'with some help from the MSDN newsgroups, I was able to get this macro to
work correctly
' written and created by Steve Buckley. (c) April 2008.
' This macro copies a single worksheet, called Sum, from the precreated
' TR claim workbook. Everything on the worksheet is already configured,
' or formatted as needed.
Dim WkBkName As String, WkBkName1 As String
Dim ShtCnt As Integer, TmpltWB As Workbook
Dim wkbkNmA As String
Workbooks.Application.ScreenUpdating = False
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")
Set TmpltWB = ActiveWorkbook
If Len(WkBkName) = 0 Then Exit Sub
WkBkName1 = WkBkName & ".xlsx"
Workbooks(WkBkName1).Activate
ShtCnt = ActiveWorkbook.Sheets.count
TmpltWB.Activate
Sheets("Page 5_Date").Select
Sheets("Page 5_Date").Copy before:=Workbooks(WkBkName1).Sheets(ShtCnt
'====================================================================================
'code below only necessary for naming abstract worksheets.
'this macro specifically for Page 5 worksheet, where none exists.
NewNm = InputBox(prompt:="Enter today's date in mm-dd-yyyy format",
Title:="New Abstract Worksheet Name")
Sheets("Page 5_Date").Name = "Pg 5_" & NewN
'===========================================================================================
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'm not sure how to describe this, so please bear with me.
I have a macro that calls to a template workbook, to copy a specific
worksheet out of it, and place it in an open workbook.
Presently, it calls for an input to give the name of the open-destination
workbook.
I'd like to modify this macro so that it no longer asks for the input of the
destination workbook's name.
The goal is to reduce the inadvertant mistakes of mis-spelling the name of
the destination workbook. For short file names this is not an issue, but for
longer file names this can become a problem.
Here is the code for the existing macro.
-----------------------------------------------------------------------------
Sub CopyPg5Sht() '(Optional Control As IRibbonControl)
'with some help from the MSDN newsgroups, I was able to get this macro to
work correctly
' written and created by Steve Buckley. (c) April 2008.
' This macro copies a single worksheet, called Sum, from the precreated
' TR claim workbook. Everything on the worksheet is already configured,
' or formatted as needed.
Dim WkBkName As String, WkBkName1 As String
Dim ShtCnt As Integer, TmpltWB As Workbook
Dim wkbkNmA As String
Workbooks.Application.ScreenUpdating = False
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")
Set TmpltWB = ActiveWorkbook
If Len(WkBkName) = 0 Then Exit Sub
WkBkName1 = WkBkName & ".xlsx"
Workbooks(WkBkName1).Activate
ShtCnt = ActiveWorkbook.Sheets.count
TmpltWB.Activate
Sheets("Page 5_Date").Select
Sheets("Page 5_Date").Copy before:=Workbooks(WkBkName1).Sheets(ShtCnt
'====================================================================================
'code below only necessary for naming abstract worksheets.
'this macro specifically for Page 5 worksheet, where none exists.
NewNm = InputBox(prompt:="Enter today's date in mm-dd-yyyy format",
Title:="New Abstract Worksheet Name")
Sheets("Page 5_Date").Name = "Pg 5_" & NewN
'===========================================================================================
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