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
1	Customer Name	Customer A
2
3	Site Details		Site 1	Site 2
4		Address
5		Town
6		State	VIC	NSW
7		Postcode
8		Contact Name
9		Contact Phone
10	Software
11		Version	7.50.499	7.40.746
12		Serial Number
13		SMA	Yes	No
14		Aniversary Date	30/04/2017

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
4	Customer	SMA	SMA Aniversary Date	Version
5	Customer 1 Site 1	Yes	30/04/2017	7.50.499	Yes	30/04/2017	7.50.499
6	ABC Site 1	Yes	1/06/2017	7.40.756	Yes	1/06/2017	7.40.756
7	No SMA Site 1	No	0/01/1900	7.40.756	No	0/01/1900	7.40.756

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.