Excel Vba Workbooks.activate Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring subscript out of range vba worksheets developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question excel vba subscript out of range windows activate x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them;
Vba Subscript Out Of Range Error 9
it only takes a minute: Sign up Run-time Error 9 for Workbooks.Activate up vote 0 down vote favorite I am having the error in Visual Basic when I try to use the function Workbooks("C:\Documents and Settings\Nega\test.xls").Activate The error says
Subscript Out Of Range Excel Vba
that: Error 9 in run time error: Subscript out of range. I saw a lot of post about extension, but my file exists and the extension is correct. It is may problems of privileges? In that case how can I add privileges in VBA for this directory? Note: If I do Workbooks("test.xls").Activate it works (Ofc, with the file into the project). So for this I think it is about privileges. Thanks for the help. excel vba excel-vba share|improve this question subscript out of range activate worksheet edited Aug 13 '15 at 10:46 asked Aug 13 '15 at 10:17 Nega developer 1919 1 Workbooks("test.xls").Activate. You say it is working, so why not just use it? –Excel Developers Aug 13 '15 at 10:23 @ExcelDevelopers But it would only works if this file is already open and I suppose Nega wants to open it if it's closed. Is it right, Nega? –mielk Aug 13 '15 at 10:31 Because I need to do out of the folder project.. Yes Mielk I want to open it, and i do with Workboos(path).Open and this works. But when I try to use Workbooks.Activate i have the runtime error –Nega developer Aug 13 '15 at 10:31 In that case you have to open it before you can activate it. Workbooks.Open FileName:=YourFullPathString –Excel Developers Aug 13 '15 at 10:39 I do, but having the same error. –Nega developer Aug 13 '15 at 10:42 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote Few things need to be fixed here: You miss colon in the filepath. It should be C:\Documents and Settings\Nega\test.xls. You have a typo in the name of the function - it should be Workbooks. You can't activate a file unless it is open, so first you needs to ensure that it is already open. You can do it with this command: Call Workbooks.Open("C:\Documents and Settings\Nega\test.xls").Activate
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site
Run Time Error 9 Subscript Out Of Range Excel 2013
About Us Learn more about Stack Overflow the company Business Learn more about workbooks filename subscript out of range hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss run time error 9 subscript out of range workbooks activate Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Subscript out of range http://stackoverflow.com/questions/31985436/run-time-error-9-for-workbooks-activate when referencing a worksheet in another workbook from a variable up vote 0 down vote favorite See picture: http://s12.postimg.org/ov8djtuh9/Capture.jpg Context: Trying to activate a sheet (variable: cSheet) in another workbook and paste data there from copied data from a different workbook. I'm getting a subscript out of range error whenever I try to activate directly using the variable (i.e. Worksheets(Name).Activate) or try to define a http://stackoverflow.com/questions/30532903/subscript-out-of-range-when-referencing-a-worksheet-in-another-workbook-from-a-v worksheet using the variable and then activate it. I've also tried other coding styles, using "With Worksheet" etc. and my code was a lot longer but I started over because every time I fix something, something else goes wrong. So, sticking to the basics. Any help would be greatly appreciated. Sub GenSumRep() Dim AutoSR As Workbook Dim asrSheet As Worksheet Dim tempWB As Workbook Dim dataWB As Workbook Dim SecName As String Dim oldcell As String Dim nsName As String Dim cSheet As Worksheet Set AutoSR = ActiveWorkbook Set asrSheet = AutoSR.ActiveSheet For a = 3 To 10 SecName = asrSheet.Range("D" & a).Value If SecName <> "" Then Workbooks.Open Range("B" & a).Value Set tempWB = ActiveWorkbook 'tempWB.Windows(1).Visible = False AutoSR.Activate Workbooks.Open Range("C" & a).Value Set dataWB = ActiveWorkbook 'dataWB.Windows(1).Visible = False AutoSR.Activate 'Copy paste data For b = 24 To 29 oldcell = Range("C" & b).Value If b = 24 Then nsName = Trim(SecName) & " Data" Set cSheet = tempWB.Sheets(nsName) Else nsName = asrSheet.Range("B" & b).Value Set cSheet = tempWB.Sheets(nsName) End If 'Copy dataWB.Activate Range(oldcell).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy 'Paste tempWB.Activate cSheet.Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.Cut
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time http://www.ozgrid.com/forum/showthread.php?t=155999 | Build Automated Trading Models In Excel | Excel Web Pages http://www.vbaexpress.com/forum/showthread.php?29184-Solved-VBA-Subscript-out-of-range-Error | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General subscript out of range error while activating workbook from a variable name Excel Training / Excel Dashboards Reports If this is your subscript out first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here subscript out of to view the relaunched Ozgrid newsletter. Results 1 to 7 of 7 Thread: subscript out of range error while activating workbook from a variable name Thread Tools Show Printable Version Search Thread Advanced Search July 13th, 2011 #1 swat View Profile View Forum Posts Member Join Date 7th June 2011 Posts 29 subscript out of range error while activating workbook from a variable name Hi, I am getting the name of a workbook A from a particular cell value in a workbook B. I am getting an subscript out of range error - Runtime error - 9. I am guessing it has somethign to do with the way I declare the variable. VB: Sub ABcopy() Dim MetricsSheet As String Workbooks("Metrics Dashboard.xlsm").Activate MetricsSheet = Workbooks("Metrics Dashboard.xlsm").Worksheets("Input").Cells(10, "D").Value If Worksheets("Data").Activate = True Then Else Worksheets("Data").Activate End If With Worksheets("Data") .Range("A1:A38").Select .Range(Selection, Selection.End(xlDown)).Copy End With [B]Application.Workbooks(MetricsSheet).Activate[/B] With Workbooks(MetricsSheet).Worksheets("Sheet1") .Range("A2").Select .Selection.End(xlToLeft).Offset(0, 1).Paste End With End Sub I am getting the error in the highlighted line. Thanks, Vivek Excel Video Tutorials / Excel Dashboards Reports Reply With Quote July 13th, 2011 #2 rory View Profile V
VBA Code & Other Help Excel Help Solved: VBA: 'Subscript out of range' Error ... If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Page 1 of 2 1 2 Last Jump to page: Results 1 to 20 of 21 Thread: Solved: VBA: 'Subscript out of range' Error ... Thread Tools Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 11-11-2009,07:14 AM #1 romainaccor View Profile View Forum Posts View Blog Entries View Articles VBAX Regular Joined Nov 2009 Posts 18 Location Solved: VBA: 'Subscript out of range' Error ... Hi guys!!! I am trying to reach and activate a worksheet within a workbook located in a folder stored on a server. So heres my code line but when I run it, it displays the 'Subscript out of range' error.... Sheets("\\servername Finance&Admin\AB FINACE SHARED FOLDER\E-REPORTING TASKS\2009\10_October\P&L\H3600.XLS[SOI]").Activate servername : I couldnt write it all cuz the website prevents posting with active links...so just assume its correctly written in my code and see if the mistake can come from smw else... H3600.XLS being the name of the workbook and SOI being the name of the worksheets, Ive proceeded to some test and those names are the REAL ones. So can you see any mistakes in my code? Thanks!!! Reply With Quote 11-11-2009,08:34 AM #2 xld View Profile View Forum Posts View Blog Entries View Articles Distinguished Lord of VBAX VBAX Grand Master Joined Apr 2005 Posts 24,522 Location If the workbook is open, just activate the book without the directory path, then activate the sheet. If it isn't, open it, then do the previous. ____________________________________________ Nihil simul inventum est et perfectum Abusus non tollit usum Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen! James Thurber Reply With Quote 11-11-2009,09:06 AM #3 romainaccor View Profile View Forum Posts View Blog Entries View Articles VBAX Regular Joined Nov 2009 Posts 1