Copy data from another workbook into current workbook when the path and filename can change

[KarenXL]

I have a workbook that creates pivot tables and charts from data that changes weekly. The data is downloaded from a customer database and exported into its own Excel spreadsheet. I am trying to copy data from that spreadsheet into a worksheet in my pivot table workbook. The name and location of that report file can change depending on who is running the excel sheet.

I believe the code will be something like this - but I need to figure out how to obtain the path and name of the workbook I am copying from. My workbook is called leaderboard and the sheet I am copying into is the DataModel sheet. The excel sheet created by the customer database has a worksheet called DataToImport that I want to pull data from.

Worksheets("DataModel").Range("A1:U1000").Value = Workbook("need to get path and name").Worksheets("DataToImport").Range("A1:U1000").Value

I'm assuming I use application.getopenfilename to obtain the path and name of the workbook, and that I place it into a variable. But I'm not clear as to what type of variable this would be (is there a workbook variable?) and exactly how to write the statement.

Any help would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub KarenXL()
   Dim Wbk As Workbook
   Dim Fname As String
   
   Fname = Application.GetOpenFilename
   Set Wbk = Workbooks.Open(Fname)
   
   ThisWorkbook.Sheets("dataModel").Range("A1:U1000").Value = Wbk.Sheets("DataToImport").Range("A1:U1000").Value
   Wbk.Close False
End Sub

 

[KarenXL]

That was exactly what I needed. Thank you so much. Us newbies can spend hours trying to figure out something that others have known for years!

 

[Fluff]

You're welcome & thanks for the feedback