Pull data from sheet in another workbook.

[NateD1]

Hi All,
in need of some help,
i have the below code which works within a workbook to copy multiple sheets to one sheet, however i need to adapt this so it does the same but pulls the data from a closed work book and paste it into a sheet in my open workbook.

example: workbook1 (current open) > run macro > opens closed workbook2 via file path (the name of the file is the same, except date at end changes daily( WB2 19.11.2020) > runs below code in WB2 to combine sheets > paste data to WB1 sheet1.

if possible to have a variable where i can change the date in a cell in WB1 before running so that it looks at the correct file name.

Sub CopyToMaster()

Application.ScreenUpdating = False

Dim i As Long
Dim Lastrow As Long
Dim ans As Long

For i = 3 To 7
With Sheets(i)
    ans = .Cells(Rows.Count, "A").End(xlUp).Row
    Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
        .Cells(2, 1).Resize(ans, 7).Copy Sheets("Master").Cells(Lastrow, 1)
End With
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Duplicate copy and stack all tabs data to one sheet in another workbook using vba

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).