 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008selecting 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. 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 > Hi. > A while back I recorded a macro to select a worksheet from one workbook, and [quoted text clipped - 32 lines] > > Thank you. Tom, Thanks for the response. The workbook is already open. Does it need to be declared if the book is open already? > You never open the workbook before trying to activate it. Try this revised > code: [quoted text clipped - 50 lines] > > > > Thank you. 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 > Tom, > Thanks for the response. [quoted text clipped - 55 lines] > > > > > > Thank you. 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. 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 > Ok.... > I removed the Workbooks.open statement. [quoted text clipped - 12 lines] > I'd initially tried Workbooks(WkBkName).select but got the "subscript out of > range" error with that too. Tom, It works. Thank you. One last question. How would I close the template workbook in VBA? Again-- thank you. > 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 [quoted text clipped - 41 lines] > > I'd initially tried Workbooks(WkBkName).select but got the "subscript out of > > range" error with that too. 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 > Tom, > It works. [quoted text clipped - 48 lines] > > > I'd initially tried Workbooks(WkBkName).select but got the "subscript out of > > > range" error with that too. Tom, Thanks again. it all works great. Best. > Easy. We already have an object variable (TmpltWB) set to the template > workbook. Add [quoted text clipped - 6 lines] > > Hutch 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.