Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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
    'DECLARE  AND SET VARIABLES
    Dim s As Variant
    Dim t As Variant
    Row = 2
    '---------------------------------------------------
    'CLEAR SHEET AND BUILD HEADER
    Cells.ClearContents
    [a1] = "Tab Id"
    [b1] = "Category"
    [c1] = "position"
    [d1] = "id"
    [e1] = "site_id"
    [f1] = "link"
    [g1] = "alt/title"
    [h1] = "image location"
    '---------------------------------------------------
    'COPY DATA FROM EACH SHEET THEN ADJUST COLUMN WIDTHS
    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
    '----------------------------------------------------
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    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:

    splits.png

    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

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for all your help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •