VBA to copy and paste from specific sheet in another workbook

[RedOctoberKnight]

First off, I want to say thank you to everyone who has helped me in the past. This site has truly become an asset in my quest to learn Excel. I am currently in the very beginning stages of trying to learn VBA for a project I’ve been working on for work and with that said, and the small time crunch I’m in, I’m hoping someone will be able to help me out once more.



I have a staffing schedule workbook that I created. What I would like to do is have it “copy and paste” data from another workbook.

I’m looking for a VBA that will do the following:

-Open up a workbook named “truckschedules”

-find a specific sheet within that workbook and copy the data from range “A2:E47”

-The schedule workbook and “Truckschedules” workbook both contain a bunch of sheets named by date. My intent is for it to be able to open the “truckschedules” and find the sheet with the same date as the current sheet I’m on in the schedule workbook and copy the data.

-Paste the data into the current workbook( schedule workbook) in cells “A2:E47”

-close the “truckschedules” workbook.



Any help would be much appreciated.

 

[John_w]

Maybe something like this:

Public Sub Copy_Range_From_Truck_Schedules()

    Dim tsWorkbook As Workbook
    
    Set tsWorkbook = Workbooks.Open(ThisWorkbook.Path & "\truckschedules.xlsx")
    With ThisWorkbook.ActiveSheet
        .Range("A2:E47").Value = tsWorkbook.Worksheets(.Name).Range("A2:E47").Value
    End With
    tsWorkbook.Close False
    
End Sub

Assumptions:
The code goes in the schedule (destination) workbook.
The "truckschedules" workbook is in the same folder and a .xlsx file.
The source sheet in the "truckschedules" workbook has the same name as the active sheet in the schedule workbook.
You only want cell values.

 

[RedOctoberKnight]

Worked perfectly! Thank you so much!