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

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,108
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top