Explaining what this VBA code means

[TheLSD]

I have a code that copies data from column C or E if there's no data on column C from all sheets on the workbook except the master sheet
But this code is not completely mine, I asked a colleague help to make it for me
What I know is that it also depends on column G
Can someone please explain to me what does each row of the code does?
Much appreciated
Thank you

Sub Copy_Detail_Capex()
    Dim ws As Worksheet, MasterSheet As Worksheet
    Dim originalDestinationCell As Range, nextDestCell As Range
    Dim firstGreyCell As Range, c, e, s As Range
    Dim lastRow, firstRow, colToCheckLast, i As Integer
    Dim isMain As Boolean
    
    Set MasterSheet = Sheets("Form Recap")            '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)
    
    firstRow = 6
    colToCheckLast = 7
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = MasterSheet.Name Then
            Set firstGreyCell = ws.Range("C" & firstRow) 'Set first starting loop cell
            lastRow = ws.Cells(ws.Rows.Count, colToCheckLast).End(xlUp).Row
            isMain = True
            For i = firstRow To lastRow
                Set c = ws.Range("C" & i)
                Set e = ws.Range("E" & i)
                Set s = Nothing
                If isMain Then
                    If c.Interior.Color = firstGreyCell.Interior.Color Then
                        If Not IsEmpty(c) Then
                            Set s = c
                        Else
                            isMain = False
                        End If
                    End If
                Else
                    If c.Interior.Color = firstGreyCell.Interior.Color Then
                        If Not IsEmpty(c) Then
                            Set s = c
                        End If
                        isMain = True
                    Else
                        If Not IsEmpty(e) Then
                            Set s = e
                        End If
                    End If
                End If
                
                If Not s Is Nothing Then
                    Set nextDestCell = MasterSheet.Cells(nextDestCell.Row + 1, originalDestinationCell.Column)
                    nextDestCell.Interior.Color = s.Interior.Color
                    nextDestCell.Value = s.Value
                End If
            Next
        End If
    Next ws
End Sub

 

[My Aswer Is This]

I suggest you tell us what you're wanting a script for you to do.
To explain in words what every line of code does would be difficult and not really provide you with a script that might work for you.

 

[TheLSD]

I suggest you tell us what you're wanting a script for you to do.
To explain in words what every line of code does would be difficult and not really provide you with a script that might work for you.

the script is copying data on column C or E with a condition that if column C does not contain data then move to column E and if column C back with data, copy the data on C column.

In code, I try to use column G as my base for the column to detect if there's data on column C or E
I try the code and sometimes it works perfectly but sometimes it doesn't in which the data contained in the cell doesn't copy to master sheet.

how can I fix this, is this problem reside on the code or the worksheet that causes that problem?

 

[Fluff]

I asked a colleague help to make it for me

Then why not ask your colleague to explain it? After all, they wrote it.

 

[TheLSD]

Then why not ask your colleague to explain it? After all, they wrote it.

sorry for the late reply, it has been almost a week (until today), and still haven't been able to contact my colleague at all

 

[Alex Blakenburg]

What I know is that it also depends on column G

If it only using Column G to determine the LastRow number, so it will stop processing rows at the last row in which Column G contains data.

    colToCheckLast = 7                                                                                           ' XXX This is column G
   
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = MasterSheet.Name Then
            Set firstGreyCell = ws.Range("C" & firstRow) 'Set first starting loop cell
            lastRow = ws.Cells(ws.Rows.Count, colToCheckLast).End(xlUp).Row        ' XXX Using column G
            isMain = True
            For i = firstRow To lastRow                                                                       ' XXX Last row based on Column G

If it not processing certain Column C values that you are expecting it to, check that the interior color / fill color of the Cell is exactly the same as that of C6 which is being used to as the comparison cell for the color.
If you want to check you could make C6 the activecell then in the immediate window and type into the immediate window (including the leading "?") the following.
? ActiveCell.Interior.Color
Then change the activecell to one of the problem cells in column C and hit enter at the end of the last time you typed the command and make sure it returns the exact same code.