In that case, eliminate the Workbooks.Open statement from the code I sent. I
think your code had either (or both ) of two problems.
- You added the whole path to the workbook name; that won't work with
Workbooks(WkBkName1).Activate
- You appended .xlsx to the workbook name. If that workbook has not been
saved yet, the ".xlsx" would cause Workbooks(WkBkName1).Activate to fail. You
may be able to just use WkBkName. It needs to match the workbook name as
displayed in the title bar.

Hutch

"SteveDB1" wrote:

Tom,
Thanks for the response.
The workbook is already open.
Does it need to be declared if the book is open already?



"Tom Hutchins" wrote:

You never open the workbook before trying to activate it. Try this revised
code:

WkBkName = InputBox(Prompt:="enter workbook name of where to copy
worksheet", Title:="Copy worksheet to existing workbook")
If Len(WkBkName) = 0 Then Exit Sub
Workbooks.Open "S:\Users Shared\UserName\DTR\Active Files\" & WkBkName &
".xlsx"
WkBkName1 = WkBkName & ".xlsx"

Sheets("Tab # ").Select
Workbooks(WkBkName1).Activate
Sheets("Tab # ").Copy After:=Workbooks(WkBkName1).Sheets(Sheets.Count)

Hope this helps.

Hutch

"SteveDB1" wrote:

Hi.
A while back I recorded a macro to select a worksheet from one workbook, and
then place that into another workbook.

I'd like to take this to the next step where I choose the workbook.
I have the following code, and get a " subscript out of range" error.

Dim WkBkName As String
Dim WkBkName1 As String

'
Workbooks.Open Filename:= _
"C:\Documents and Settings\UserName\Application
Data\Microsoft\Templates\TR Claim Book.xlt" _
, Editable:=True

WkBkName = InputBox(Prompt:="enter workbook name of where to copy
worksheet", Title:="Copy worksheet to existing workbook")

WkBkName1 = "S:\Users Shared\UserName\DTR\Active Files\" & WkBkName & ".xlsx"



Sheets("Tab # ").Select
Workbooks(WkBkName1).Activate
Sheets("Tab # ").Copy After:=Workbooks(WkBkName1).Sheets(Sheets.Count)



When I click the debug button, and mouse over the line that's highlighted in
yellow

"Workbooks(WkBkName1).Activate"

it shows the name of my file in a small message box.
what am I missing?

Thank you.