Copy unique Worksheet Data from multiple workbooks into a Masterfile with multiple unique sheets. Please help!

[Manerlao]

Hi All

I am new to the forum and have a problem with my VBA code.
Please could someone kindly provide some support with my code, thank you.
So far another community member has helped me on another thread, but now my situation has changed for the data ingestion.

Current code:
Works well to ingest data from a workbook if all the sheets are the same name in the Source and Destination Workbook.
But I need to copy 1 sheet from the source workbook into the corresponding sheet in Masterfile worksheet. However, the worksheet name in Masterfile is a shorter name of the source. Please read my explanations below. Thank you.

Sub t()
Dim fName As Variant, sh As Worksheet, wb As Workbook
CYCLE:
fName = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", Title:="Please select a file")
    If fName = False Then Exit Sub
Set wb = Workbooks.Open(fName)
    For Each sh In ThisWorkbook.Sheets
        wb.Sheets(sh.Name).UsedRange.Offset(1).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
    Next
    ans = MsgBox("Workbook " & Mid(fName, InStrRev(fName, "\") + 1) & " is complete.  Do you want to continue?", _
        vbYesNo, "CONTINUE?")
        wb.Close False
    If ans = vbYes Then GoTo CYCLE:
End Sub

Please see my details below:

The Objective:

I have a Masterfile in Excel with the following sheet names:
Sheet1, Sheet 2, …, SheetN

I would like to copy/Paste data into the Masterfile [Destination] under each sheet from multiple workbooks [source] across different years of data.
Essentially, paste the data under the last row of each dataset per sheet within my Masterfile [Destination].
The data in the [source] workbook and worksheet are within A2:[LastColumn and LastRow] and I need to paste them into the respective Masterfile worksheets under the last row data.
I need the option to open the file first, so I can select it.

The [source] workbooks contain the following details/format:
Please note, the names of the workbooks are exactly the same as the sheet names. If this helps at all.

i.e.
Data in my 2015 data folder on Windows:
The name of Workbook1 = “Sheet1_2015” as well.
The name of Workbook2 = “Sheet2_2015”


My data (all workbooks contain 1 unique sheet only):

Workbooks for 2015:
Workbook1 contains: Sheet1_2015
Workbook2 contains: Sheet2_2015
…
Workbook30 contains: Sheet30_2015

Workbooks for 2016:
Workbook1 contains: Sheet1_2016
Workbook2 contains: Sheet2_2016
...
Workbook30 contains: Sheet30_2016

Workbooks for 20XX
Workbook1 contains: Sheet1_20XX
Workbook2 contains: Sheet2_20XX
…
Workbook30 contains: Sheet30_20XX


As you can see, the sheet name per workbook has an additional year at the end. So the VBA needs to concatenate the worksheet name, lookup the sheet name in the Masterfile and paste it into the corresponding Masterfile sheet under the previous data, accordingly.

For example:
Data from A2:[LastColumn and LastRow] in 2016 workbook1 “Sheet1_2016” needs to be pasted into the Masterfile “Sheet1” under the last data in the sheet.
Data from A2:[LastColumn and LastRow] in 2019 workbook7 “Sheet7_2019” needs to be pasted into the Masterfile “Sheet7” under the last data in the sheet.
…
Data from A2:[LastColumn and LastRow] in 20XX workbookX “SheetX_20XX” needs to be pasted into the Masterfile “SheetX” under the last data in the sheet.


Please could someone help? I spent the whole night/morning adjusting the code that another member (JLG) kindly sent me, but my attempt in VBA failed.
Thank you.

Kind regards