selecting workbook

[SteveDB1]

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.

 

[Tom Hutchins]

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]

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

 

[Tom Hutchins]

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]

Ok....
I removed the Workbooks.open statement.
I also tried both WkBkName, and WkBkName1.
Both give the same error as before. "subscript out of range"

When you say- "if the WkBkName file hasn't been saved yet, it won't work" --
you're talking about an initial saveAs, correct?
The file already is saved with a file name. The reason that I've done this
is because we have to update these files on a regular basis, and so we're
adding a worksheet for each data package we're processing, and then modifying
some data on other worksheets.
The worksheet that I'm adding is from a template file, and I just want to
pull a copy over from a formatted template with all of the raw formats, sheet
layouts, etc...... on it.
I'd initially tried Workbooks(WkBkName).select but got the "subscript out of
range" error with that too.

 

[SteveDB1]

Tom,
I just thought of something.
Are there any characters that VBA will not recognize when activating,
copying to, etc..., a workbook?

I have an underscore and a comma in the file name.

I.e., DTR-ABX_Apr10,2008.xlsx is my file name, where ABX are numbers we use
to identify the file number.

DTR is just an internal notation element.

I renamed the file to remove the comma and that didn't change anything.
Thanks again.

 

[Tom Hutchins]

I'm getting the error now on the .Copy statement, not the .Activate
statement. I think Excel is returning the number of sheets in the workbook
you opened from your template, not the number of sheets in the destination
workbook. Try this:

Dim WkBkName As String, WkBkName1 As String
Dim ShtCnt As Integer, TmpltWB As Workbook

Workbooks.Open Filename:= _
"C:\Documents and Settings\UserName\Application Data\Microsoft\Templates\TR
Claim Book.xlt" _
, 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)

'When all done...
Set TmpltWB = Nothing

Hutch

 

[SteveDB1]

Tom,
It works.
Thank you.
One last question.
How would I close the template workbook in VBA?
Again-- thank you.

 

[Tom Hutchins]

Easy. We already have an object variable (TmpltWB) set to the template
workbook. Add

TmpltWB.Close SaveChanges:=False

before freeing the object variable with

Set TmpltWB = Nothing

Hutch

 

[SteveDB1]

Tom,
Thanks again.
it all works great.
Best.