2 inputs consolidated live

bartclip

New Member
Joined
Nov 27, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

New Member
Joined
Nov 27, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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
1606927212470.png


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
1606927368633.png


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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top