Copying table to another sheet on worksheet change

[LePig]

Hi,



I need to copy a table from one worksheet to another when something is added. It is within the worksheet_change event.

However i keep getting 424 error code


Dim KeyCells_2 As Range


Set KeyCells_2 = Sheets("Sheet1").Range("Table3[[All],[Languages]]").Value


' Me.Range ("C152:C169")


If Not Application.Intersect(KeyCells_2, Target) Is Nothing Then
Application.EnableEvents = False
Sheets("Sheet1").Select
Sheets("Sheet1").Range("Table3[[#All],[Languages]]").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C6").Select
ActiveSheet.Paste
End If


Any help would be greatly appreciated.

Kind Regards

[LePig]

Sorry ignore the if statement the problem seems to be occuring when trying to set Keycells. Any help with this syntax would be appreciated.

[SamT]

What is this supposed to mean?

Table3[[All],[Languages]]

[LePig]

Hi Sam T,

Table3[[All],[Languages]] is the table i am referencing.

Table3(Name of table)[[All]all fields in the table, [Languages is the defined range of the table]

[SamT]

Try
Set KeyCells_2 = Sheets("Sheet1").Range("Languages")

But do not use .Value because Keycells_2 is a Range

I suggest

Dim KeyCells_2 As Range

Set KeyCells_2 = Sheets("Sheet1").Range("Languages")
Application.EnableEvents = False

If Not Application.Intersect(KeyCells_2, Target) Is Nothing Then _
KeyCells_2.Copy Dest:=Sheets("Sheet2").Range("C6")

Application.EnableEvents = True