name of workbook...

[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

 

[Jim Rech]

There's nothing in Excel that presents a list of open workbook names to pick
from. You'd probably have to create a userform with a list box containing
the workbooks. I've attached a quick and dirty example.

--
Jim
| 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_" & 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
| ----------------------------------------------------------------
| Thank you.
| SteveB.
|