AW: Gleiche Spalteninhalte zusammenführen
22.04.2015 17:30:05
Nepumuk
Hallo,
teste mal:
Option Explicit
Public Sub CollectTables()
Const COLLECTIONSHEET_NAME As String = "Zusammenfassung" 'Name der neuen Tabelle
Dim objWorksheet As Worksheet, objCollection As Worksheet
Dim objCell As Range
Dim lngColumn As Long, lngInsertColumn As Long, lngInsertRow As Long
For Each objWorksheet In Worksheets
If objWorksheet.Name = COLLECTIONSHEET_NAME Then
Application.DisplayAlerts = False
objWorksheet.Delete
Application.DisplayAlerts = True
Exit For
End If
Next
Set objCollection = Worksheets.Add(Before:=Worksheets(1))
objCollection.Name = COLLECTIONSHEET_NAME
For Each objWorksheet In Worksheets
If Not objWorksheet Is objCollection Then
With objWorksheet
For lngColumn = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
Set objCell = objCollection.Rows(1).Find( _
What:=.Cells(1, lngColumn).Value, LookIn:=xlValues, LookAt:=xlWhole)
If objCell Is Nothing Then
lngInsertColumn = lngInsertColumn + 1
Call .Range(.Cells(1, lngColumn), .Cells(.Rows.Count, lngColumn).End(xlUp)).Copy( _
Destination:=objCollection.Cells(1, lngInsertColumn))
Else
With objCollection
lngInsertRow = .Cells(.Rows.Count, objCell.Column).End(xlUp).Row + 1
End With
Call .Range(.Cells(2, lngColumn), .Cells(.Rows.Count, lngColumn).End(xlUp)).Copy( _
Destination:=objCollection.Cells(lngInsertRow, objCell.Column))
End If
Next
End With
End If
Next
objCollection.Columns.AutoFit
Set objCollection = Nothing
Set objCell = Nothing
End Sub
Gruß
Nepumuk