Hi, so I need to modify my code
here's my code
Code
Sub Macro2()
Dim ws As Worksheet, MasterSheet As Worksheet
Dim originalDestinationCell As Range, nextDestCell As Range
Dim firstGreyCell As Range, rangeToSearchIn As Range, c As Range
Set MasterSheet = Sheets("Sheet1") 'where you want to put the copied data
Set originalDestinationCell = MasterSheet.Range("C6") 'the first cell the data will be copied to
Set nextDestCell = originalDestinationCell.Offset(-1, 0)
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = MasterSheet.Name Then
Set firstGreyCell = ws.Range("C6")
Set rangeToSearchIn = ws.Range("C6:C1500") 'the range that the data is in
For Each c In rangeToSearchIn
If IsEmpty(c) = False Then 'only copy if the cell is not blank
If c.Interior.Color = firstGreyCell.Interior.Color Then
'if the interior color of cell 'c' is the same as 'firstGreyCell' then
Set nextDestCell = MasterSheet.Cells(nextDestCell.Row + 1, originalDestinationCell.Column)
'move the next cell down one column and back to the original column
nextDestCell.Value = c.Value 'copy the value to the recap sheet
nextDestCell.Interior.Color = c.Interior.Color 'copy the cell color too
Else
'if the interior color is not the same as 'c'
Set nextDestCell = nextDestCell.Offset(0, 2) 'move the nextDestCell to the right by 1
nextDestCell.Value = c.Value 'copy its value
End If
End If
Next c
End If
Next ws
End Sub
Display More
the code works but it needs some adjustment if the C column blank, move to column E then copy the data contained there and if there's data on next row of column c, back to C then copy it
here's what the data looks like
Project | Detail |
Project A | |
laptop | |
monitor | |
keyboard | |
*blank* | |
phone | |
mouse | |
project c | |
windows | |
license |
the result of the code
Project |
Project A |
Project C |
What I want
Project |
Project A |
Phone |
Mouse |
Project C |
what should I change in order to have the data I want?
thank you