Smart VBA Sheet Matcher

[paypal56_ab6mk6y7]

Create a macro for Excel called **Smart VBA Sheet Matcher** that automates the following tasks:

1. There are two Excel files: **First File** and **Second File**.
2. Each file contains a column with addresses (e.g., column A).
3. The macro should:
- Take each address from the **First File**.
- Find the matching address in the **Second File**.
- If a match is found, copy the additional data from the row in the **Second File** and insert it into new columns in the corresponding row in the **First File**.
4. Important requirements:
- Both files should be selected through a file picker dialog.
- Data from the **Second File** is appended to the **First File** without altering any original data.
- All new data is written to new columns in the **First File**, after the existing ones.
- If an address from the **First File** is not found in the **Second File**, no action is taken for that row.

**Expected Outcome:**
- After running the **Smart VBA Sheet Matcher**, the **First File** contains its original data plus additional columns with information retrieved from the **Second File** based on matching addresses.
- The **Second File** remains unchanged.

**Test Data:**
1. **First File**
| Address | Data 1 | Data 2 |
|--------------|--------|--------|
| Address 1 | 123 | abc |
| Address 2 | 456 | def |
| Address 3 | 789 | ghi |

2. **Second File**
| Address | Data A | Data B | Data C |
|--------------|--------|--------|--------|
| Address 2 | x1 | y1 | z1 |
| Address 1 | x2 | y2 | z2 |

**Result:**
**First File after running Smart VBA Sheet Matcher:**
| Address | Data 1 | Data 2 | Data A | Data B | Data C |
|--------------|--------|--------|--------|--------|--------|
| Address 1 | 123 | abc | x2 | y2 | z2 |
| Address 2 | 456 | def | x1 | y1 | z1 |
| Address 3 | 789 | ghi | | | |

**Notes:**
- The macro should be dynamic, capable of handling files of different lengths and varying numbers of columns.
- Data matching is based solely on the "Address" column in both files.

By implementing **Smart VBA Sheet Matcher**, you can efficiently synchronize and enrich your Excel files with minimal manual effort.

[paypal56_ab6mk6y7]

The macro performs the following steps:

1. Uses file picker dialogs to select the **First File** and **Second File**.
2. Matches addresses between the two files.
3. Copies additional data from the matching rows in **Second File** and appends it to new columns in the **First File**.

### VBA Code:

```vba

Code: Select all

Sub SmartVBASheetMatcher()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook
    Dim rng1 As Range, rng2 As Range
    Dim cell1 As Range
    Dim matchRow As Long
    Dim lastCol As Long
    Dim colOffset As Long
    Dim filePath1 As String, filePath2 As String

    ' Open file picker dialog for First File
    filePath1 = Application.GetOpenFilename("Excel Files (*.xlsx; *.xlsm), *.xlsx; *.xlsm", , "Select the First File")
    If filePath1 = "False" Then Exit Sub
    Set wb1 = Workbooks.Open(filePath1)
    Set ws1 = wb1.Sheets(1)

    ' Open file picker dialog for Second File
    filePath2 = Application.GetOpenFilename("Excel Files (*.xlsx; *.xlsm), *.xlsx; *.xlsm", , "Select the Second File")
    If filePath2 = "False" Then Exit Sub
    Set wb2 = Workbooks.Open(filePath2)
    Set ws2 = wb2.Sheets(1)

    ' Define address ranges
    Set rng1 = ws1.Columns(1).SpecialCells(xlCellTypeConstants) ' Addresses in Column A of First File
    Set rng2 = ws2.Columns(1).SpecialCells(xlCellTypeConstants) ' Addresses in Column A of Second File

    ' Find the last column in First File to start appending new data
    lastCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

    ' Loop through each address in the First File
    Application.ScreenUpdating = False
    For Each cell1 In rng1
        ' Look for the matching address in the Second File
        On Error Resume Next
        matchRow = Application.Match(cell1.Value, rng2, 0)
        On Error GoTo 0

        If matchRow > 0 Then
            ' Copy additional data from the matching row in Second File to new columns in First File
            For colOffset = 2 To ws2.Cells(matchRow, ws2.Columns.Count).End(xlToLeft).Column
                ws1.Cells(cell1.Row, lastCol + colOffset - 1).Value = ws2.Cells(matchRow, colOffset).Value
            Next colOffset
        End If
    Next cell1
    Application.ScreenUpdating = True

    ' Close the Second File without saving
    wb2.Close SaveChanges:=False

    ' Notify the user
    MsgBox "Matching and data transfer completed successfully!", vbInformation
End Sub

```
### How to Use:
1. Open Excel and go to the VBA editor (`Alt + F11`).
2. Create a new module (`Right-click in the VBA Project > Insert > Module`).
3. Paste the above code into the module.
4. Run the macro (`F5` or `Run` from the menu).
5. Use the file picker dialogs to select the **First File** and **Second File**.

### Expected Outcome:
- The **First File** will have additional columns with data from the **Second File** for rows where addresses match.
- The **Second File** will remain unchanged.