Error 1004 Excel Range
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 excel error 1004 method range of object _worksheet failed Us Learn more about Stack Overflow the company Business Learn more about hiring
Excel Error 1004 Method Range Of Object _global Failed
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the excel error 1004 select method of range class failed 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 VBA error 1004 - select method
Error 1004 Excel Vba
of range class failed up vote 13 down vote favorite 2 First time poster, so if there is any formatting, or guidelines I failed to adhere to, please let me know so that I can fix it. So I am basically asking the user for the file directory of the excel file, then I setup some variables (originally set at public as project variables, since these error 1004 excel macro were being used and changed in other places). I have also added the lines to set these variables to nothing (just in case, I do not think that it should matter). I then set these variables to the excel file, workbook, and sheets that I want to access. Dim filepath as String filePath = CStr(fileDialog) 'ask file dir, set to string Dim sourceXL As Variant 'these three were orig project variables Dim sourceBook As Variant Dim sourceSheet As Variant Dim sourceSheetSum As Variant Set sourceXL = Nothing 'set to nothing in case...? Set sourceBook = Nothing Set sourceSheet = Nothing Set sourceSheetSum = Nothing Set sourceXL = Excel.Application 'set to the paths needed Set sourceBook = sourceXL.Workbooks.Open(filePath) Set sourceSheet = sourceBook.Sheets("Measurements") Set sourceSheetSum = sourceBook.Sheets("Analysis Summary") Dim measName As Variant 'create variable to access later Dim partName As Variant sourceSheetSum.Range("C3").Select 'THIS IS THE PROBLEM LINE measName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value sourceSheetSum.Range("D3").Select partName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value So I created two different sheet variables 'sourceSheets' and 'sourceSheetsSum', the code works if i use 'sourceSheets', but error 1004 occurs if i use 'sourceSheetsSum'. I have also tried the code with the variable 'sourceSheet' removed completely, in case that was overriding
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
Error 1004 Excel 2013
the company Business Learn more about hiring developers or posting ads with us Stack
Run Time Error 1004 Excel Vba
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of visual basic error 1004 excel 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Excel VBA: Run-time error '1004': Select method of Range class failed up vote 0 down vote favorite I http://stackoverflow.com/questions/10949893/vba-error-1004-select-method-of-range-class-failed am writing a macro to work in an excel sheet that is saved within a template then exported to .xls or .xlsx from a separate application. This macro is to copy two columns 'x' number of times depending on a quantity that is entered into a cell. Sub Matrix_Quantity() Dim x As Integer x = ActiveWorkbook.Sheets("Inspection Sampling Matrix").Cells(11, 4) Dim n As Integer n = x - 1 For numtimes = 1 http://stackoverflow.com/questions/22207182/excel-vba-run-time-error-1004-select-method-of-range-class-failed To n 'Loop by using x as the index number to make x number copies. Sheets("Inspection Report").Columns("F:G").Select Selection.Copy Selection.Insert Shift:=x1 + nToRight Next End Sub The problem I am having is that when the macro is run withing the template (.xlt) it runs fine. As soon as the template is converted to .xls or .xlsx it spots working and gives a runtime error. When debugging the macro it highlights Sheets("Inspection Report").Columns("F:G").Select My feeling is that it is looking to select the columns in the .xlt workbook but when converted to .xls or .xlsx it is still trying to look for the .xlt workbook and I'm not sure how or why its doing this. excel vba excel-vba share|improve this question edited Feb 6 '15 at 21:55 RubberDuck 5,65322458 asked Mar 5 '14 at 19:21 user3384820 1111 1 it's because your sheet Inspection Report isn't active at the moment macro runs. Read how to avoid using Select/Active statements, please. You could replace your code with Sheets("Inspection Report").Columns("F:G").Copy and Sheets("Inspection Report").Columns("F:G").Insert Shift:=x1 + nToRight –simoco Mar 5 '14 at 19:22 Thanks for the response. When I plug in the code you recommended and run the macro Excel will crash with no error. It simply brings up the windows error "Microsoft Excel has Stopped Working" –user338482
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 http://stackoverflow.com/questions/4378239/run-time-error-1004-when-trying-to-copy-a-range-excel-vba Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question 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; it only takes a minute: Sign up Run-time error '1004' when trying to copy a range (Excel VBA) up vote 1 down error 1004 vote favorite 1 I am trying to copy a range of cells for which I don't know the last row and column (though I can easily get to those using variables). Unfortunately though, the way I am trying to reference a range is giving me a run-time error 1004 (Application-defined or object-defined error) when I use variables, and I can't figure out why. Below is a sample of the code: Dim error 1004 excel wkbk As Workbook Dim copy_rng As Range ... Set copy_rng = wkbk.Worksheets("Payable").Range("A1:Y3500") Set copy_rng = wkbk.Worksheets("Payable").Range(Cells(1, 1), Cells(last_row_pay, last_col_pay)) The first Set statement is simply an example, and it works fine (so I know wkbk is properly defined and it's finding the "Payable" worksheet). Does anybody know why the second Set statement would not work? Is there a syntax issue? (During debug, if I hover over the last_row_pay and last_col_pay variables, I can see valid values--1533 and 25 respectively.) Thanks for any help. vba excel-vba share|improve this question asked Dec 7 '10 at 15:18 rryanp 53541534 add a comment| 4 Answers 4 active oldest votes up vote 4 down vote accepted Use the resize function, because the Cells() function works for the active worksheet and it might get confused. Dim wkbk As Workbook Dim copy_rng As Range Dim pay_rows As Integer ,pay_columns As Integer ... pay_rows = 3500 pay_columns = 23 Set copy_rng = wkbk.Worksheets("Payable").Range("A1").Resize(pay_rows,pay_columns) where A1 is the top left cell of your data. share|improve this answer edited Dec 7 '10 at 20:18 answered Dec 7 '10 at 16:07 ja72 14.2k13377 Very well spotted! You can indeed replicate the problem by activating a sheet other than "Payable". –Chris Spicer Dec 7 '10 at 16:29 I