VBA error 1004 - select method of range class failed

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

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

Option ExplicitSub 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 LINEEnd 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.

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.SelectsourceSheetSum.Range("C3").Select

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 ObjectDim sourceSheet As Object, sourceSheetSum As ObjectDim lRow As LongDim measName As Variant, partName As VariantDim filepath As Stringfilepath = CStr(FileDialog)'~~> Establish an EXCEL application objectOn Error Resume NextSet sourceXL = GetObject(, "Excel.Application")'~~> If not found then create new instanceIf Err.Number <> 0 Then    Set sourceXL = CreateObject("Excel.Application")End IfErr.ClearOn Error GoTo 0Set sourceBook = sourceXL.Workbooks.Open(filepath)Set sourceSheet = sourceBook.Sheets("Measurements")Set sourceSheetSum = sourceBook.Sheets("Analysis Summary")lRow = sourceSheetSum.Range("C" & sourceSheetSum.Rows.Count).End(xlUp).RowmeasName = sourceSheetSum.Range("C3:C" & lRow)lRow = sourceSheetSum.Range("D" & sourceSheetSum.Rows.Count).End(xlUp).RowpartName = sourceSheetSum.Range("D3:D" & lRow)