2 inputs consolidated live

[bartclip]

Hi All,

I'm trying to come up with a workbook to allow me to consolidate data from 2 input tabs onto 3rd tab in real time.

So I have "Input A" tab and "Input B" tab, the data is being entered onto both tabs at the same time and i would like to see combined data from both tabs on tab 3 in real time as data is being inputed.

Sorry guys its probably something simple but just can't wrap my head around it.

Thanks for your help in advance.

 

[sijpie]

There is a pretty easy way to do this depending on how the data is being added and what you mean with real time.

If there are automated processes like sensors dumping data into each of the sheets, then that could mean concurrent access, which makes it slightly more difficult. If data is added intermittently than probably a simple macro could do.

How many columns of input is there on each row?
Do you want to see them added to the same table, with a label in front to tell you which sheet the data came from?

 

[bartclip]

Hi Sijpie,

Its a simple spreadsheet where you have 4 columns per input sheet. Column A- time stamp, B- barcode scan (Numbers only), C- Barcode scan mixture of characters and D- quantity.

I need both inputs to consolidate on sheet 3, live as data is being entered.

Do you want to see them added to the same table, with a label in front to tell you which sheet the data came from? - Yes.

I've tried number of macros but can't work out how to update sheet 3 live without assigning macro to the button on the screen.

Thanks for your help in advance

 

[sijpie]

The answer lies in worksheet events. Normally macros run when the user presses a button or selects a macro to run. But Windows is an event run system. Windows watches what the user does, but also what other applications do. So if you click the mouse button, all it does is send an event to Windows. Windows then checks where the mouse cursor was and which button was pressed. It passes this event then to the application over which window the event happend, for the application to process. Hey a long story as an introduction.

You can use these events as well. Each sheet (and the workbook) has its own private code area. Here YOU can access the events that happen to that particular sheet (or workbook). One of the events for a sheet is the Change event. When Excel notifies the sheet that a change has been made to the sheet, this event is run. Normally it just does the default stuff, but you can tell it do do something else.

So how do you get there?
Two ways: 1) From Excel, right click on the sheet tab name and select 'view code', or
2) from the VBA editor (VBE) in the left top panel, you see the workbook with all the sheets listed. Double click on the sheet name.

This opens the sheet code module.
Just above the right pane you see two dropdowns. In the left one select Worksheet

This will inmmediatly result in that the VBE creates the SelectionChange event in the window. We don't want that one, we want the Change event. So in the right hand dropdown select Change

Now you can write a macro telling the sheet what to do when a change to the sheet has occurred.

So what you need is if a change occurred to say a cell in column D of the sheet to then copy the range A:D of that row, add the sheet name and paste it to the next available row in Sheet3.

COpy paste the below code into the VBE overwriting anything there

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lR As Long
    Dim vIn As Variant
    
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        'cell changed is in column D
        'copy the values in that row to an array
        vIn = Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Value
        'add a column to vIn for the sheet name
        ReDim Preserve vIn(1 To 1, 1 To 5)
        vIn(1, 5) = Me.Name     'Me is this sheet
        
        'now get the next empty row on sheet 3
        With Sheets("Sheet 3")  '<<<<< edit sheet name as required
            lR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            ' and dump the array to the available row
            .Cells(lR, 5).Value = vIn
        End With
    End If
End Sub