here's a simplified example with color for what i want to do with vba.
i have the first and principal range with data , A to G (7 column), a second range from I to O (7 column) , third from Q to W (7 column)...
i use this macro to find duplicate and cut destination .
lastCol = rngData.Columns.Count
lastRow = rngData.Rows.Count
For I = lastRow To 1 Step -1
For J = lastCol To 1 Step -1
Set ACell = rngData.Cells(I, J)
For Each Cell In rngData
If Cell <> Empty And _
Cell.Value = ACell.Value And _
Cell.Address <> ACell.Address Then
Cell.ClearContents "clear duplicated cells in the first row"
Acell.cut destination:= Acell.offset(0,8)" move duplicated cells in the last row "
n = n + 1
End If
Next Cell
Next J
Next I
after that i have now two ranges with data , first range (A to G ) and second range from (I to O)."sheet T1"
now in sheet T2 i have duplicated value.
what i want is instead of Acell.cut destination:= Acell.offset(0,8) become Acell.cut destination:= Acell.offset(0,16) "sheet T3"
only if the duplicated value are found in the second range and so on for the third range ...
Acell.offset(0,8) become Acell.offset(0,16) become Acell.offset(0,32)...