Trying to populate master worksheet with info from smaller worksheets RRS feed

  • Question

  • Hi All - 

    Having a heck of a time trying to solve this challenge. I'm responsible for creating a Master Calendar in Excel (UGH) but I need that Master Calendar Worksheet to be populated by the 4 other tabs (Currently: Industry, Trade / Content, Product / Internal, NGS / Impact Dates). 

    I'm trying to figure out how to roll up each of the lines from those four tabs (4 columns in each total). The information is formatted exactly the same, and includes dates and text. It's not quite a data consolidation in the sense that theres no data to average, it's mostly text. But if you think of it that way - I want one sheet that shows everything on all the other sheets, also auto-sorted by date.

    Any thoughts for how to get this merged into one sheet that autoupdates if someone changes something on the other tabs? (and even better if i can be updated from the Master as well!). 

    I have NO experience with VBA but am trying to learn the code language to be able to do this in the future.


    Thursday, April 7, 2016 8:24 PM

All replies

  • Is the column structure between worksheets the same? Like, Industry's column A has the same header of Impac Dates, the data begins in the same row, etc...? -

    Thursday, April 7, 2016 9:17 PM
  • Yes, all of the columns are the same (there are five total for each sheet) and include date, location, event, and location, and category. All data starts at A4 under 3 lines of headers.
    Friday, April 8, 2016 3:41 PM
  • Your workbook has a design error. One of the thumb rules is "don't split into multiple worksheets data with the same structure". You could just put all of them in one worksheet and create a 6th column called Area, for example, and so this would be populated with Industry, Trade / Content, Product / Internal, NGS / Impact Dates.

    It is much easier to consolidate data this way. Is there a strong reason to make them separated?


    By the way, you can use macros to consolidate (merge) data of your worksheets into a single worksheet using this free add-in: -

    Friday, April 8, 2016 7:21 PM
  • They need to stay separated per preference of the user. the columns themselves may evolve over time but the need to build an autoupdating master sheet will always be there.

    The master sheet is really an overview of the data sheets, so without as much detail. The data isn't "data" as in, revenue or numbers, it's mostly text and information, dates, and descriptions.

    The new challenge is figuring out how to get two columns (date and event) to populate on the master sheet using a date row setup. So for instance, three of the four sheets may have different events on 6/6/16, so how do I set the sheet up to pull those events into the 6/6/16 master sheet row in their respective columns? 

    The manual labor piece of this is taxing on such a small department, hence the need for an easier solution.

    Friday, April 8, 2016 7:38 PM
  • "They need to stay separated per preference of the user."
    That's why I hate bosses. They insist handling data in the wrong way. It would be easier and safer to create an UserForm to fill in the information in controls, validate and then write to the master worksheet.


    I'm understanding that data from each child worksheet grows (in number of rows) over time, as new events occur. So, a 100% linked master worksheet would be complicated to code. My suggestion is for you to create a new worksheet called Master, and a button called Refresh. The write the code:

    Option Explicit
    Const WORKSHEET_HEADERS As String = "Date,Event,Value,Column4,Column5"
    Const MASTER_WORKSHEET_NAME As String = "Master"
    Dim aConnection As Object 'ADODB.Connection
    Dim aLastColumn As String
    Dim aMasterWorksheet As Worksheet
    Sub Refresh()
        Dim rs As Object 'ADODB.Recordset
        Dim sb As Collection
        Dim Sql As String
        Dim SelectFields As String
        Set aConnection = GetConnection
        Set aMasterWorksheet = ThisWorkbook.Worksheets(MASTER_WORKSHEET_NAME)
        SelectFields = GetSelectFields
        Set sb = New Collection
        sb.Add "select " & SelectFields
        sb.Add "from " & GetWorksheetRange("Industry")
        sb.Add "union all"
        sb.Add "select " & SelectFields
        sb.Add "from " & GetWorksheetRange("Trade-Content")
        Sql = GetSql(sb)
        Set rs = GetRecordset(Sql)
        aMasterWorksheet.Range("A2").CopyFromRecordset rs
        rs.Close: Set rs = Nothing
        aConnection.Close: Set aConnection = Nothing
    End Sub
    Private Sub ClearMasterData()
        With aMasterWorksheet
            .Range("A2").Resize(.Rows.Count - 2 - 1).ClearContents
        End With
    End Sub
    Private Function GetWorksheetRange(pWorksheetName As String) As String
        Dim ws As Worksheet
        Dim Result As String
        Dim TotalColumns As Long
        Dim RangeAddress As String
        Dim LastRow As Long
        Set ws = ThisWorkbook.Worksheets(pWorksheetName)
        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        Result = "[" & pWorksheetName & "$A4:E" & LastRow & "]"
        GetWorksheetRange = Result
    End Function
    Private Sub GetTotalHeaders()
        Dim NumHeaders As Long
        Dim Headers() As String
        Headers = Split(WORKSHEET_HEADERS, ",")
        NumHeaders = UBound(Headers) - LBound(Headers) + 1
        aLastColumn = Split(aMasterWorksheet.Columns(NumHeaders).Address(False, False), ":")(0)
    End Sub
    Private Function GetSelectFields() As String
        Dim iSelectField As Variant
        Dim Result As String
        Dim SelectFields() As String
        SelectFields = Split(WORKSHEET_HEADERS, ",")
        For Each iSelectField In SelectFields
            Result = Result & "[" & iSelectField & "], "
        Next iSelectField
        Result = Left(Result, Len(Result) - Len(", "))
        GetSelectFields = Result
    End Function
    Private Function GetRecordset(pSql As String) As Object 'ADODB.Recordset
        Dim Result As Object 'ADODB.Recordset
        Set Result = CreateObject("ADODB.Recordset")
        Result.CursorLocation = 3 'adUseClient
        Result.Open pSql, aConnection, 0, 1 'adOpenForwardOnly,adLockReadOnly
        Set GetRecordset = Result
    End Function
    Private Function GetConnection() As Object 'ADODB.Connection
        Dim Result As Object 'ADODB.Connection
        Set Result = CreateObject("ADODB.Connection")
        Result.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                              "Data Source=" & ThisWorkbook.FullName & ";" & _
                              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        Set GetConnection = Result
    End Function
    Private Function GetSql(pCollection As Collection) As String
        Dim Result As String
        Dim iLine As Variant
        For Each iLine In pCollection
            Result = Result & iLine & vbNewLine
        Next iLine
        GetSql = Result
    End Function

    Friday, April 8, 2016 10:59 PM