Option Explicit
Sub ForOne()
Dim oWbMaster As Excel.Workbook, oWbSlave As Excel.Workbook, oWbk As Excel.Workbook
Dim oWsMaster As Excel.Worksheet, oWsSlave As Excel.Worksheet
Dim rngMaster As Range, rngSlave As Range, c As Range
On Error GoTo Abbruch
Set oWbMaster = Workbooks.Open(Filename:=Dateipfad("Masterdatei wählen"))
Set oWsMaster = oWbMaster.ActiveSheet
Set oWbSlave = Workbooks.Open(Filename:=Dateipfad("Slavedatei wählen"))
Set oWsSlave = oWbSlave.ActiveSheet
Set rngMaster = oWsMaster.UsedRange
rngMaster.Interior.Color = xlNone
For Each c In rngMaster
If oWsSlave.Range(c.Address).Value <> c.Value Then
c.Value = oWsSlave.Range(c.Address).Value
c.Interior.Color = RGB(230, 230, 230)
End If
Next c
Set rngSlave = oWsSlave.UsedRange
For Each c In rngSlave.Columns
If c.Column > rngMaster.Columns.Count Then
c.Copy oWsMaster.Cells(1, c.Column)
End If
Next c
For Each c In rngSlave.Rows
If c.Row > rngMaster.Rows.Count Then
c.Copy oWsMaster.Cells(c.Row, 1)
End If
Next c
Abbruch:
For Each oWbk In Application.Workbooks
If oWbk.Name <> ThisWorkbook.Name Then oWbk.Close True
Next oWbk
End Sub
Private Function Dateipfad(ByVal strTitel As String) As String
Dim objFileDialog As Office.FileDialog
Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFilePicker)
With objFileDialog
.AllowMultiSelect = False
.ButtonName = "Übernehmen"
.Filters.Clear
.Filters.Add "Excel", "*.xls; *.xlsx; *.xlsm"
.InitialView = msoFileDialogViewList
.Title = strTitel
If .Show = -1 Then
Dateipfad = .SelectedItems(1)
End If
End With
End Function