Problem retrieving data from another worksheet

[Rocky0201]

Hi,

I am having problems retrieving a range of cells from another worksheet.

The following ignores the With/End With and uses the range on the current sheet.

Private Sub Worksheet_Activate()

    EnableEvents = True
    
    Call BuildMilestoneTasks
        
    MDRows = Sheets("Survey Definitions").Cells(Rows.Count, 1).End(xlUp).Row
    MDColumns = Sheets("Survey Definitions").Cells(3, Columns.Count).End(xlToLeft).Column
    
    With Sheets("Survey Definitions")
    
        TmpArray = Range(Cells(5, 1), Cells(MDRows, MDColumns))
    
    End With
    
    Call BuildStatus

    Application.Goto Range("A1:A1"), scroll:=False
    
    EnableEvents = False
    
End Sub

The following produces a runtime error - Unable to get the Select Property of the Worksheet class

Private Sub Worksheet_Activate()

    EnableEvents = True
    
    Call BuildMilestoneTasks
        
    MDRows = Sheets("Survey Definitions").Cells(Rows.Count, 1).End(xlUp).Row
    MDColumns = Sheets("Survey Definitions").Cells(3, Columns.Count).End(xlToLeft).Column
    
    With Sheets("Survey Definitions").Select
    
        TmpArray = Range(Cells(5, 1), Cells(MDRows, MDColumns))
    
    End With
    
    Call BuildStatus

    Application.Goto Range("A1:A1"), scroll:=False
    
    EnableEvents = False
    
End Sub

Any help would greatly be appreciated.

Thanks,

 

[Jonmo1]

When you use a With Structure, you must precede range and cells command with a period in order to reference the object referenced by the With Statement.

Try

    With Sheets("Survey Definitions")
        TmpArray = .Range(.Cells(5, 1), .Cells(MDRows, MDColumns))
    End With

 

[Rocky0201]

Thanks Jonmo1...

I now get - Application-defined or object-defined error.

I tried using the code right after my Worksheet_Activate in my Sheet module and also in my Public module. Same error occurs.

I'm not certain what I am doing wrong.