Póster por primera vez, así que si hay algún formato o guía que no pueda cumplir, hágamelo saber para poder solucionarlo.

Básicamente, le estoy pidiendo al usuario el directorio de archivos del archivo de Excel, luego configuro algunas variables (originalmente establecidas en público como variables de proyecto, ya que éstas se estaban usando y modificando en otros lugares). También he agregado las líneas para configurar estas variables a nada (por si acaso, no creo que deba importar). Luego configuro estas variables para el archivo de Excel, el libro de trabajo y las hojas a las que quiero acceder.

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

Así que creé dos variables de hoja diferentes 'sourceSheets' y 'sourceSheetsSum', el código funciona si uso 'sourceSheets', pero el error 1004 se produce si uso 'sourceSheetsSum'. También probé el código con la variable 'sourceSheet' eliminada por completo, en caso de que se haya invalidado 'sourceSheetSum' por alguna razón.

Estoy bastante seguro de que el libro de Excel y las hojas existen y se están llamando correctamente, ya que ejecuté un poco de código rápido para recorrer todas las hojas del libro y mostrar los nombres, como se muestra a continuación.

For j = 1 To sourceBook.Sheets.Count
Debug.Print (Sheets(j).name)
Next j

Con la salida de depuración de

Mediciones
Resumen del análisis
Ajustes de análisis

Entonces, ¿alguien tiene alguna idea de lo que podría significar este error, o cómo puedo encontrar más información sobre lo que realmente es el error?

EDITAR: Así que decidí agregar un poco a la lista de los nombres de las hojas, sin estar seguro de que sea de alguna ayuda.

For j = 1 To sourceBook.Sheets.Count
    listSheet(j) = Sheets(j).name
Next j    
Debug.Print (listSheet(2))    
Set sourceSheetSum = sourceBook.Sheets(listSheet(2))

La depuración imprime el Resumen de análisis, por lo que sé que la hoja existe en el libro de trabajo, y no debería haber ningún problema con un "error tipográfico" en los nombres.
Sin embargo, el código sigue teniendo el mismo error en la misma línea.

deusxmach1na: creo que querías que cambiara

Dim sourceXL As Variant                  
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant

Set sourceSheet = sourceBook.Sheets("Measurements")

A

Dim sourceXL As Excel.Application
Dim sourceBook As Excel.Workbook
Dim sourceSheet As Worksheet
Dim sourceSheetSum As Worksheet

Set sourceSheet = sourceBook.Worksheets("Measurements")

Pero esto no cambia el error, recuerdo que lo tuve similar a eso, y luego lo cambié ya que leí que la variante es como una captura de todos, no realmente tan sólida en qué variante es.