| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting workbook
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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" wrote: 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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
Tom,
It works. Thank you. One last question. How would I close the template workbook in VBA? Again-- thank you. "Tom Hutchins" wrote: 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" wrote: 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
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" wrote: Tom, It works. Thank you. One last question. How would I close the template workbook in VBA? Again-- thank you. "Tom Hutchins" wrote: 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" wrote: 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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
selecting workbook
Tom,
Thanks again. it all works great. Best. "Tom Hutchins" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting Different Workbook | Excel Programming | |||
| Selecting data from another workbook | Excel Programming | |||
| Selecting data from 1 workbook to copy and paste to a 2nd workbook | Excel Programming | |||
| Selecting a worksheet in a workbook | Excel Programming | |||
| Selecting workbook | Excel Programming | |||
Linear Mode
