VBA error 1004 - select method of range class failed

Question

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 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  sourceSheetSum  for some reason   I am fairly confident that the excel workbook and sheets exist and are being called correctly  since I ran a quick bit of code to loop through all the sheets in the workbook and output the names  shown below   For j   1 To sourceBook Sheets Count Debug Print  Sheets j  name  Next j   With the debug output of     Measurements   Analysis Summary   Analysis Settings   So  does anyone have any ideas what this error could mean  or how I can possibly go about finding more about what the error actually is   EDIT  So I decided to add a bit to the listing of the sheet names  not sure if it will help at all   For j   1 To sourceBook Sheets Count     listSheet j    Sheets j  name Next j     Debug Print  listSheet 2       Set sourceSheetSum   sourceBook Sheets listSheet 2     The debug prints Analysis Summary  so I know that the sheet exists in the workbook  and there should not be any issues with a  typo  in the names   The code still has the same error at the same line though   deusxmach1na  I think you wanted me to change   Dim sourceXL As Variant                   Dim sourceBook As Variant Dim sourceSheet As Variant Dim sourceSheetSum As Variant  Set sourceSheet   sourceBook Sheets  Measurements     To   Dim sourceXL As Excel Application Dim sourceBook As Excel Workbook Dim sourceSheet As Worksheet Dim sourceSheetSum As Worksheet  Set sourceSheet   sourceBook Worksheets  Measurements     But this does not change the error  I remember I had it similar to that  and then changed it since I read that variant is like a catch all  not actually that solid on what variant is

User · Accepted Answer

You have to select the sheet before you can select the range.

I've simplified the example to isolate the problem. Try this:

Option Explicit


Sub RangeError()

    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim sourceSheetSum As Worksheet

    Set sourceBook = ActiveWorkbook
    Set sourceSheet = sourceBook.Sheets("Sheet1")
    Set sourceSheetSum = sourceBook.Sheets("Sheet2")

    sourceSheetSum.Select

    sourceSheetSum.Range("C3").Select           'THIS IS THE PROBLEM LINE

End Sub

Replace Sheet1 and Sheet2 with your sheet names.

IMPORTANT NOTE: Using Variants is dangerous and can lead to difficult-to-kill bugs. Use them only if you have a very specific reason for doing so.

User · Answer

Removing the range select before the copy worked for me  Thanks for the posts

User · Answer

You can t select a range without having first selected the sheet it is in  Try to select the sheet first and see if you still get the problem   sourceSheetSum Select sourceSheetSum Range  C3   Select

User · Answer

assylias and Head of Catering have already given your the reason why the error is occurring   Now regarding what you are doing  from what I understand  you don t need to use Select at all  I guess you are doing this from VBA PowerPoint  If yes  then your code be rewritten as  Dim sourceXL As Object  sourceBook As Object Dim sourceSheet As Object  sourceSheetSum As Object Dim lRow As Long Dim measName As Variant  partName As Variant Dim filepath As String  filepath   CStr FileDialog       gt  Establish an EXCEL application object On Error Resume Next Set sourceXL   GetObject    Excel Application        gt  If not found then create new instance If Err Number  lt  gt  0 Then     Set sourceXL   CreateObject  Excel Application   End If Err Clear On Error GoTo 0  Set sourceBook   sourceXL Workbooks Open filepath  Set sourceSheet   sourceBook Sheets  Measurements   Set sourceSheetSum   sourceBook Sheets  Analysis Summary    lRow   sourceSheetSum Range  C   amp  sourceSheetSum Rows Count  End xlUp  Row measName   sourceSheetSum Range  C3 C   amp  lRow   lRow   sourceSheetSum Range  D   amp  sourceSheetSum Rows Count  End xlUp  Row partName   sourceSheetSum Range  D3 D   amp  lRow

[vb.net] VBA error 1004 - select method of range class failed

Examples related to vb.net

Examples related to excel

Examples related to excel-2010

Examples related to powerpoint

Examples related to vba