Denormalize a variable number of sheets with variable number of rows into a Summary sheet

Topic

New Reply

Need some Excel 2007 code for the following

I have the attached Excel WorkBook with a variable number of Sheets, each Sheet has a variable number of rows

I want to denormalize a variable number of sheets with variable number of rows into a Summary sheet

I need some VBA code to loop through and create a Summary Sheet, see sample workbook, that has the first cell (Tab id & Category) from each sheet A1

and creates Summary Sheet with the following 8 columns:

Tab id, Category, position, id, site_id, link, alt / title, image location

Note: Summary sheet has desired sample output results that can be overridden

Reply | Quote

Replies

John,

Here is some code placed in the worksheet_activate event subroutine of the Summary sheet. Every time the sheet is activated, it will refresh with the most current data from the other sheets. Also, the columns will auto fit the data. You can add as many sheets, even blank ones, with as many rows as you like. Just make sure the Summary sheet is always the first sheet and it retains the name “Summary” or you will need to change it in the code. The rest of the sheets can be in any order and the code is not dependent on their name, so you can change them as you please.

HTH,
Maud

Code:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
On Error Resume Next
[COLOR=”#008000″]’DECLARE  AND SET VARIABLES[/COLOR]
Dim s As Variant
Dim t As Variant
Row = 2
[COLOR=”#008000″]’—————————————————
‘CLEAR SHEET AND BUILD HEADER[/COLOR]
Cells.ClearContents
[a1] = “Tab Id”
[b1] = “Category”
[c1] = “position”
[d1] = “id”
[e1] = “site_id”
[f1] = “link”
[g1] = “alt/title”
[h1] = “image location”
[COLOR=”#008000″]’—————————————————
‘COPY DATA FROM EACH SHEET THEN ADJUST COLUMN WIDTHS[/COLOR]
For I = 2 To Worksheets.Count
    With Worksheets(I)
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    s = Split(.[a1], “: “)
    t = Split(s(1), “, “)
    For J = 4 To lastrow

        Cells(Row, 1) = t(0)
        Cells(Row, 2) = t(1)
        For K = 1 To 8
            Cells(Row, K + 2).Value = .Cells(J, K).Value
        Next K
        Row = Row + 1
    Next J
    End With
Next I
Columns(“A:H”).EntireColumn.AutoFit
[COLOR=”#008000″]’—————————————————-[/COLOR]
Application.ScreenUpdating = True
End Sub

Reply | Quote

Maud, Thanks for the wonderful clinic

Could you verify my contents of the spllits?

Split (text_string, delimiter, limit, compare)

‘Input to split: Tab id: 470641, Home

s = Split(.[a1], “: “) ‘Output: s = 470641, Home

t = Split(s(1), “, “) ‘Output: t = 470641 Home

Cells(Row, 1) = t(0) ‘470641

Cells(Row, 2) = t(1) ‘Home

Could you translate the following syntax into english?

Cells(Row, K + 2).Value = .Cells(J, K).Value

Reply | Quote

Yes John, you are correct in your analysis of the split values

Could you translate the following syntax into english?

Cells(Row, K + 2).Value = .Cells(J, K).Value

The variable “Row” represents the row of the Summary sheet while the J represents the row on the other sheets. Both are increment with passes through the J loop. K represents the column on the other sheets but since the matching columns on the Summary sheet are offset by 2 because of the Tab id and Home columns, the columns on the Summary sheet must be represented by K+2. As K increments through the K loop, every column on the other sheets (designated by K) will have it matching column on the Summary sheet (designated by K+2).

HTH,
Maud

Reply | Quote

Thanks for that

t = 470641 Home

Does t(0) take the 1st value up to the space?

Cells(Row, 1) = t(0) ‘470641

Does t(1) take the remaining value?

Cells(Row, 2) = t(1) ‘Home

Where can I read up sub scripting?

Reply | Quote

John,

In the second split, s(1) is split at the “, ” meaning that up to (but not including) the comma is t(0) and after the space is t(1) Here is the breakdown:

35124-splits

Even though the delimiter can be more than one character, it splits the string and then the delimiter itself is removed from either string.

HTH,
Maud

Reply | Quote

Thanks for all your help

Reply | Quote