[Copy Data] from specific (Sheet1) in [Source Workbook] & Paste to same (sheet1) in [Target MASTERFILE Workbook]. Loop for [Multiple] Sheet Names

[Manerlao]

Dear Excel Community!

I hope everyone is keeping well!
I have been having a serious issue with the following VBA code. If someone could very kindly help I would be extremely grateful!! Thank you all.

Objective:

[Copy] Data 'usedrange' from cell A2 (Down to last row and across to last column of data) from source workbook in worksheet with name [Sheet1] > and then > [Paste] in target workbook Masterfile with the same name [Sheet1] under the latest data in that table.
[Copy] Data 'usedrange' from cell A2 (Down to last row and across to last column of data) from source workbook in worksheet with name [Sheet2] > and then > [Paste] in target workbook Masterfile with the same name [Sheet2] under the latest data in that table.
...
[Copy] Data 'usedrange' from cell A2 (Down to last row and across to last column of data) from source workbook in worksheet with name [SheetN] > and then > [Paste] in target workbook Masterfile with the same name [SheetN] under the latest data in that table.

I need to loop this for all matching sheet names in source file to Masterfile.

So the sheet name will be the unique identifier.

Workbook Structure:

I have multiple sheets in the source workbook which are named: Sheet1, Sheet2, Sheet3, ..., SheetN. There may be more or fewer sheets in this workbook.
I have around 50 sheets in the Target Masterfile Workbook which are named: Sheet1, Sheet2, Sheet3, ..., Sheet40.
The sheets of data I want to copy from source WB will have exactly the same name as the Sheets in the Masterfile WB. So this should make things simple.

My VBA code works superbly to do exactly the above, but only if the source workbook has the same number of sheets in the same order as the Masterfile workbook.
But what I need is for the Macro to 'Lookup and MATCH' the worksheet name and match this with the worksheet name in Masterfile and then copy the data across under the last data in Masterfile sheet corresponding to source sheet from source workbook.

Sub AddDataToMasterfile()

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 incorporated.  Do you want to add more files?", _
        vbYesNo, "Add more?")
        wb.Close False
    If ans = vbYes Then GoTo CYCLE:
 
Worksheets("Sheet1").Select
MsgBox ("Workbook is now Ready.")

End Sub

Please let me know if anyone has some ideas? This has been challenging me for the whole weekend!!
If you need more information or clarification, please let me know!

Thank you and I hope to hear from the community!!
Manerlao

 

[Fluff]

How about

   For Each sh In ThisWorkbook.Sheets
      If ShtExists(sh.Name, wb) Then
         wb.Sheets(sh.Name).UsedRange.Offset(1).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
      End If
   Next

and use this function

Public Function ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

 

[Manerlao]

Hi again Fluff! We meet again
Hope you have been well.

Please pardon me, but since I'm still new to VBA, how would I structure the incorporated VBA Function?
Would it just be in the Macro Module under all the other code?

Like this?:

Sub AddDataToMasterfile()

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
'Fluff code
      If ShtExists(sh.Name, wb) Then
         wb.Sheets(sh.Name).UsedRange.Offset(1).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
      End If
   Next

    ans = MsgBox("Workbook " & Mid(fName, InStrRev(fName, "\") + 1) & " is incorporated.  Do you want to add more files?", _
        vbYesNo, "Add more?")
        wb.Close False
    If ans = vbYes Then GoTo CYCLE:
 
Worksheets("Sheet1").Select
MsgBox ("Workbook is now Ready.")

End Sub




Public Function

ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
    If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0

End Function

Thank you! I'll try this now to check.

 

[Fluff]

Would it just be in the Macro Module under all the other code?

Yes, that will be fine. As long as it's in a standard module, then you should be ok.

 

[Manerlao]

Fluff, I am always speechless when you provide such elegant code! How do you do it! Seriously, thank you so much for this one.
I just tested it and it works perfectly!

This whole weekend was a nightmare for me and you solved it in a moment!
Thank you so much!

Best regards,
M.

 

[Fluff]

You're welcome & thanks for the feedback.