Small change then:
Public Sub TransposeContracts()
Dim contractSheet As Worksheet
Dim overviewSheet As Worksheet
Dim lastRow As Long
Dim thisRow As Long
Dim dict As Object
Dim nextRow As Long
Dim nextCol As Long
Set contractSheet = Sheets("Contracts")
Set overviewSheet = Sheets("Overview")
Set dict = CreateObject("Scripting.Dictionary")
' Clear out overview sheet
With overviewSheet
.Range("A2:K" & .Cells(.Rows.Count, "A").End(xlUp).Row).ClearContents
End With
nextRow = 1
With contractSheet
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For thisRow = 2 To lastRow
If .Cells(thisRow, "A").Value <> .Cells(thisRow - 1, "A").Value Then
dict.RemoveAll
If overviewSheet.Cells(nextRow, "A").Value <> "" Then nextRow = nextRow + 1
nextCol = 1
End If
If .Cells(thisRow, "C").Value <> "" Then
If Not dict.Exists(.Cells(thisRow, "C").Value) Then
dict.Add .Cells(thisRow, "C").Value, ""
overviewSheet.Cells(nextRow, "A").Value = .Cells(thisRow, "A").Value
nextCol = nextCol + 1
overviewSheet.Cells(nextRow, nextCol).Value = .Cells(thisRow, "B").Value
End If
End If
Next thisRow
End With
End Sub
WBD
Bookmarks