Formatting of data on a Summary sheet

[Muzza77]

Hi,

I am having trouble with formatting of data on a summary sheet as I need to loop through a couple of loops to collect the data, this is where my issue is I think.

I have a worksheet for each customer and this worksheet can contain the details for multiple sites - each site in a new column.

I am trying to loop through each worksheet (I have this working for only one site as the cell references are static), my trouble starts when I need to loop through each site on the worksheet before moving to the next worksheet.

Below is a sample of the data on a worksheet. All worksheets have the same format as they are generated from a template.

Excel 2013 64 bit

	A	B	C	D
Customer Name	Customer A

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFF00"]Site Details[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00"]Site 1[/TD]
[TD="bgcolor: #FFFF00"]Site 2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF99"]Address[/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF99"]Town[/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF99"]State[/TD]
[TD="bgcolor: #FFFF99"]VIC[/TD]
[TD="bgcolor: #FFFF99"]NSW[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF99"]Postcode[/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF99"]Contact Name[/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF99"]Contact Phone[/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]
[TD="bgcolor: #FFFF99, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFC000"]Software[/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]
[TD="bgcolor: #FFD755"]Version[/TD]
[TD="bgcolor: #FFD755"]7.50.499[/TD]
[TD="bgcolor: #FFD755"]7.40.746[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]
[TD="bgcolor: #FFD755"]Serial Number[/TD]
[TD="bgcolor: #FFD755, align: right"][/TD]
[TD="bgcolor: #FFD755, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]
[TD="bgcolor: #FFD755"]SMA[/TD]
[TD="bgcolor: #FFD755"]Yes[/TD]
[TD="bgcolor: #FFD755"]No[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFC000, align: right"][/TD]
[TD="bgcolor: #FFD755"]Aniversary Date[/TD]
[TD="bgcolor: #FFD755, align: right"]30/04/2017[/TD]
[TD="bgcolor: #FFD755, align: right"][/TD]

</tbody>

Customer 1​

This is what the master worksheet looks like after running the update.

Excel 2013 64 bit

	A	B	C	D	E	F	G
Customer 1 Site 1	Yes	7.50.499	Yes	7.50.499
ABC Site 1	Yes	7.40.756	Yes	7.40.756
No SMA Site 1	No	7.40.756	No	7.40.756

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]SMA[/TD]
[TD="align: center"]SMA Aniversary Date[/TD]
[TD="align: center"]Version[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30/04/2017[/TD]

[TD="align: right"]30/04/2017[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/06/2017[/TD]

[TD="align: right"]1/06/2017[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0/01/1900[/TD]

[TD="align: right"]0/01/1900[/TD]

</tbody>

Master​

On the master worksheet I'd like the Customer column to contain a list of the "Customer + Site" and the detail for each of these on the same row in the corresponding columns.

My current (and as yet incomplete) code is below:

Private Sub UpdateSummary_Click()


Dim wsMASTER As Worksheet, Sh As Worksheet
Dim Basebook As Workbook
Dim myCell As Range
Dim SiteRange As Range
Dim ColNum As Integer
Dim RwNum As Long


Set wsMASTER = ThisWorkbook.Worksheets("Master")
    wsMASTER.Rows("5:" & wsMASTER.Rows.Count).Clear


Set Basebook = ThisWorkbook
RwNum = 4


    For Each Sh In Basebook.Worksheets
        If Sh.Name <> wsMASTER.Name And Sh.Visible Then
            ColNum = 1
            RwNum = RwNum + 1
            
            Set SiteRange = Sh.Range("C3:C10")
            For Each Cell In SiteRange
                SiteCol = 3
                SiteRow = 3
                If Cell.Value <> "" Then
                    wsMASTER.Cells(RwNum, 1).Value = Sh.Name & " " & Sh.Cells(SiteRow, SiteCol).Value
                    
                    For Each myCell In Sh.Range("C13:C14,C11")
                        ColNum = ColNum + 1
                        wsMASTER.Cells(RwNum, ColNum).Formula = _
                        "='" & Sh.Name & "'!" & myCell.Address(False, False)
                    Next myCell
                End If
            Next Cell
        End If
    Next Sh


MsgBox "Master Sheet Updated."
End Sub

I'm pretty sure the issue is somewhere in the cell references and where to increment counters etc. Just not sure how to go about it in code.

Regards,

Muzza.

 

[sericom]

Does this do what you're after

Private Sub UpdateSummary_Click()
    Dim wsMASTER As Worksheet, Sh As Worksheet
    Dim Basebook As Workbook
    Dim SiteRange As Range
    Dim RwNum As Long
    Dim lCol As Long
    Dim cCol As Long
    
    Set wsMASTER = ThisWorkbook.Worksheets("Master")
    wsMASTER.Rows("5:" & wsMASTER.Rows.Count).Clear
    Set Basebook = ThisWorkbook
    RwNum = 5
    For Each Sh In Basebook.Worksheets
        lCol = Sh.Cells(3, Sh.Columns.Count).End(xlToLeft).Column
        If Sh.Name <> wsMASTER.Name And Sh.Visible Then
            Set SiteRange = Sh.Range(Sh.Cells(3, 3), Sh.Cells(3, lCol))
            For Each Cell In SiteRange
                cCol = Cell.Column
                wsMASTER.Cells(RwNum, 1).Value = Sh.Name & " " & Sh.Cells(3, cCol).Value
                wsMASTER.Cells(RwNum, 2).Value = Sh.Cells(13, cCol).Value
                wsMASTER.Cells(RwNum, 3).Value = Sh.Cells(14, cCol).Value
                wsMASTER.Cells(RwNum, 4).Value = Sh.Cells(11, cCol).Value
                RwNum = RwNum + 1
            Next Cell
        End If
    Next Sh
    MsgBox "Master Sheet Updated."
End Sub

 

[Muzza77]

Yep, spot on.

Thank you, Sericom.