Merge worksheets based on sheet name

zakasnak

Active Member
Joined
Sep 21, 2005
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
I have two workbooks with some tabs having the same name. Columns are all the same, but rows are different. I would like to pull the data from one workbooks' sheets into the sheet with the same name in the other workbook; if the sheet name doesn't exist, I would like to pull in the entire sheet.

At the moment, I am cutting & pasting.... I'm finally into the "Cs"... HELP!

If it's possible, I'll post more specific data.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I tested this quickly. It seems to work, but will not work if one file is Excel 2003 and the other is excel 2007. Anyway, let me know if there is any furthur info needed. Just use this in a macro

Code:
Sub MatchSheets()
    Dim openName As String
    Dim firstWB As String
    Dim secondWB As String
    Dim firstSheet As String
    Dim secondSheet As String
    Dim sa As String
    Dim x As Integer, i As Integer
    
    firstWB = ActiveWorkbook.Name
    openName = Application.GetOpenFilename("Excel Workbook (*.xls), *.xls")
    Workbooks.Open (openName)
    secondWB = ActiveWorkbook.Name
    
    Workbooks(firstWB).Activate
    
    For i = 1 To ActiveWorkbook.Sheets.Count
        firstSheet = Sheets(i).Name
        Workbooks(secondWB).Activate
        For x = 1 To ActiveWorkbook.Sheets.Count
            secondSheet = Sheets(x).Name
            If firstSheet = secondSheet Then
                Workbooks(firstWB).Activate
                Sheets(i).Select
                Range(Range("A1"), Range("A65536").End(xlUp)).EntireRow.Copy  'if there are headers to skip change "A1" to first Row with data i.e. "A2" or "A5"
                Workbooks(secondWB).Activate
                Sheets(x).Select
                Range("A65536").End(xlUp).Offset(1, 0).Select
                ActiveSheet.Paste
                Exit For
            ElseIf x = ActiveWorkbook.Sheets.Count And firstSheet <> secondSheet Then
                Workbooks(firstWB).Activate
                Sheets(i).Copy After:=Workbooks(secondWB).Sheets(x) 'this will copy unique sheets from the first Workbook to the end of the second Workbook
                Workbooks(secondWB).Activate
            End If
        Next
        Workbooks(firstWB).Activate
    Next
    
End Sub
 
Upvote 0
I'll give it a shot. I'm still in 2003, so that won't be an issue. Thank you!
 
Upvote 0
I am in awe of the know-how on this board. This worked PERFECTLY! Thank you so much ....
 
Upvote 0
very glad to help. Wish I would have caught you sooner so you wouldn't have had to go all the way to the "E"s.
 
Upvote 0

Forum statistics

Threads
1,225,156
Messages
6,183,227
Members
453,152
Latest member
ChrisMd

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
Back
Top