Linking 2 identical tables in the same workbook so when one updates the other updates

[willow1985]

I am not skilled in power query or power pivot and am wondering if someone could advise how I can link 2 identical tables in the same workbook?
One table is on a hidden sheet and the other is for anyone to see.
The visible one we will call TableSee and the Hidden one we can call TableHidden.

I would like it when you add data to TableSee it automatically updates TableHidden. They only have 2 columns and are identical in every way.

Help on this would be greatly appreciated. I have tried links and other information online in power query but I could never get either table to update when new information was added.

Thank you to anyone who can help!

 

[willow1985]

Is the data in the two sheets limited to columns A and B in See Table and H and I in the Hidden table or do the sheets contain data in other columns?

On "See" there is only the table in column A and B, and in "Hidden" there is information in other columns but the table only exists in Column H and I

 

[Sergius]

Hello! If you say that both tables are identical, then you can create a request to Power Query on the first (visible) and, based on it, build a second table (hidden). After that, in the request settings, install the update interval.

 

[willow1985]

Hello! If you say that both tables are identical, then you can create a request to Power Query on the first (visible) and, based on it, build a second table (hidden). After that, in the request settings, install the update interval.

I am not very skilled with Power Query and do not know how to do this.

 

[mumps]

Replace the current code with this:

Dim str As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        str = Target.Cells(1).Address(0, 0)
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        Sheets("TableHidden").Range(str).Offset(, 7).Resize(, 2).Delete shift:=xlUp
    Else
        Sheets("TableHidden").Range(Target.Address(0, 0)).Offset(, 7) = Target
    End If
End Sub

 

[willow1985]

Replace the current code with this:

Dim str As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        str = Target.Cells(1).Address(0, 0)
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.CountLarge > 1 Then
        Sheets("TableHidden").Range(str).Offset(, 7).Resize(, 2).Delete shift:=xlUp
    Else
        Sheets("TableHidden").Range(Target.Address(0, 0)).Offset(, 7) = Target
    End If
End Sub

I am getting a Compile error: Argument not optional on line str = Target.Cells(1).Address(0,0)

Quality Log.xlsm
	A	B
1	Employee	Email
2	Emp1	Emp1@email.com
3	Emp2	Emp2@email.com
4	Emp3	Emp3@email.com
5	Emp4	Emp4@email.com
6	Emp5	Emp5@email.com
7	Emp6	Emp6@email.com
8	Emp7	Emp7@email.com
9	Emp8	Emp8@email.com
10	Emp9	Emp9@email.com
11	Emp10	Emp10@email.com
12	Emp11	Emp11@email.com
13	Emp12	Emp12@email.com
14	Emp13	Emp13@email.com
15	Emp14	Emp14@email.com
16	Emp15	Emp15@email.com
Employee List

Quality Log.xlsm
	A	B	C	D	E	F	G	H	I
1	Confidential info1		Confidential info13		Confidential info25	Confidential info30		Employee	Email
2	Confidential info2		Confidential info14		Confidential info26	Confidential info31		Emp1	Emp1@email.com
3	Confidential info3		Confidential info15		Confidential info27	Confidential info32		Emp2	Emp2@email.com
4	Confidential info4		Confidential info16		Confidential info28			Emp3	Emp3@email.com
5	Confidential info5		Confidential info17		Confidential info29			Emp4	Emp4@email.com
6	Confidential info6		Confidential info18		Confidential info30			Emp5	Emp5@email.com
7	Confidential info7		Confidential info19					Emp6	Emp6@email.com
8	Confidential info8		Confidential info20					Emp7	Emp7@email.com
9	Confidential info9		Confidential info21					Emp8	Emp8@email.com
10	Confidential info10		Confidential info22					Emp9	Emp9@email.com
11	Confidential info11		Confidential info23					Emp10	Emp10@email.com
12	Confidential info12		Confidential info24					Emp11	Emp11@email.com
13	Confidential info13		Confidential info25					Emp12	Emp12@email.com
14								Emp13	Emp13@email.com
15								Emp14	Emp14@email.com
16								Emp15	Emp15@email.com
Hidden

 

[Alex Blakenburg]

You are missing the first line of the code mumps gave you.
Dim str as String

 

[mumps]

@Alex Blakenburg
Thanks for picking up on that.

 

[willow1985]

You are missing the first line of the code mumps gave you.
Dim str as String

Thank you! I missed that. Code works perfectly! Thank you very much mumps!

 

[mumps]

You are very welcome.