Import from a closed workbook into a specific worksheet

Try this and let me know

Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0
Dim myws As Worksheet
mysheet = "TEST"  'You specify your worksheet here
Set myws = Application.ActiveWorkbook.Sheets(mysheet)

Dim tArray As Variant, r As Long, c As Long
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(n, "A").Select
    tArray = ReadDataFromWorkbook("D:\Operations\Performance\Data\Sales\MTD Sales PC03", "A2:P15000")
    For r = LBound(tArray, 2) To UBound(tArray, 2)
        For c = LBound(tArray, 1) To UBound(tArray, 1)
            myws.Cells.Range("A2").Activate 'You specify where you want the active cell to be
            ActiveCell.Offset(r, c).Formula = tArray(c, r)
        Next c
    Next r
Application.Calculation = xlAutomatic
    On Error Resume Next
    
    On Error GoTo 0
End Sub

Select allOpen in new window

I'm getting an error:

Activate method of Range class failed.

I don't want to specify what the active cell is. I just want the code to import it to my "detail" worksheet, not the worksheet I am currently on. This way if I'm on another worksheet and I run the macro it will import to the "detail" worksheet always.

OK, try this - you need to activate at least a cell so your other array formula to work.

Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0
Dim myws As Worksheet
mysheet = "detail"  'You specify your worksheet here
Set myws = Application.ActiveWorkbook.Sheets(mysheet)

Dim tArray As Variant, r As Long, c As Long
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(n, "A").Select
    tArray = ReadDataFromWorkbook("D:\Operations\Performance\Data\Sales\MTD Sales PC03", "A2:P15000")
    For r = LBound(tArray, 2) To UBound(tArray, 2)
        For c = LBound(tArray, 1) To UBound(tArray, 1)
            myws.Range("A1").Activate 'You specify where you want the active cell to be
            ActiveCell.Offset(r, c).Formula = tArray(c, r)
        Next c
    Next r
Application.Calculation = xlAutomatic
    On Error Resume Next
    
    On Error GoTo 0
End Sub

Select allOpen in new window

Still getting the same error message on this line of code:

myws.Range("A1").Activate 'You specify where you want the active cell to be

OK I see...maybe it is better to set the active cell outside the loop...
try this please

Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0
Dim myws As Worksheet
mysheet = "DETAIL"  'You specify your worksheet name, need to insert exact name here
Set myws = Application.ActiveWorkbook.Sheets(mysheet)

Dim tArray As Variant, r As Long, c As Long
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(n, "A").Select
myws.Select
myws.Activate
myws.Range("A2").Select
myws.Range("A2").Activate 'You specify where you want the active cell to be
tArray = ReadDataFromWorkbook("D:\Operations\Performance\Data\Sales\MTD Sales PC03", "A2:P15000")
    For r = LBound(tArray, 2) To UBound(tArray, 2)
        For c = LBound(tArray, 1) To UBound(tArray, 1)
            ActiveCell.Offset(r, c).Formula = tArray(c, r)
        Next c
    Next r
Application.Calculation = xlAutomatic
    On Error Resume Next
    
    On Error GoTo 0
End Sub

Select allOpen in new window

if this does not work I will need a small sample of your MTD Sales file so I can have the same data and try to recreate the reading of the array into the spreadsheet, ok?

That worked but the problem now is I want it to copy this data to the first blank row on my "detail" worksheet. Right now it's overwriting everything starting from row A2. I need it to add this data to the first blank row so it doesn't overwrite the existing data.

OK, we need a small function to find the first blank row, cell A<blankrow> correct?

Getting an error that says:

"Object Required"

It goes to this line in the code:

Set MyRange = ws.UsedRange.End(xlDown).Offset(1, 0)