title: VBA error 1004 - select method of range class failed
body: <p>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. </p>
  
  <p>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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>For j = 1 To sourceBook.Sheets.Count
  Debug.Print (Sheets(j).name)
  Next j
  </code></pre>
  
  <p>With the debug output of</p>
  
  <blockquote>
    <p>Measurements<br>
    Analysis Summary<br>
    Analysis Settings</p>
  </blockquote>
  
  <p>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?</p>
  
  <p>EDIT:
  So I decided to add a bit to the listing of the sheet names, not sure if it will help at all.</p>
  
  <pre><code>For j = 1 To sourceBook.Sheets.Count
      listSheet(j) = Sheets(j).name
  Next j    
  Debug.Print (listSheet(2))    
  Set sourceSheetSum = sourceBook.Sheets(listSheet(2))
  </code></pre>
  
  <p>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. <br>
  The code still has the same error at the same line though.</p>
  
  <p>deusxmach1na: I think you wanted me to change </p>
  
  <pre><code>Dim sourceXL As Variant                  
  Dim sourceBook As Variant
  Dim sourceSheet As Variant
  Dim sourceSheetSum As Variant
  
  Set sourceSheet = sourceBook.Sheets("Measurements")
  </code></pre>
  
  <p>To </p>
  
  <pre><code>Dim sourceXL As Excel.Application
  Dim sourceBook As Excel.Workbook
  Dim sourceSheet As Worksheet
  Dim sourceSheetSum As Worksheet
  
  Set sourceSheet = sourceBook.Worksheets("Measurements")
  </code></pre>
  
  <p>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.</p>