Create new sheet

[AbuFawaz]

so I have 2 sheets and need to create a third one as follows:

Sheet1
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Name [/TD]
[TD="width: 64"]Code [/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD]A[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]A002[/TD]
[TD]B[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]A003[/TD]
[TD]C[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]A004[/TD]
[TD]D[/TD]
[TD]C4
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Code[/TD]
[TD="width: 64"]ID_2[/TD]
[TD="width: 64"]Data[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]B001[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]B002[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]B003[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]B004[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]B005[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]B006[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]B007[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]B008[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]B009[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]B010[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B011[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B012[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B013[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B014[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B015[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B016[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B017[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]B018[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

I need sheet3 to be created as:
ID ID_2 Data


Many thanks in advance.

 

[Fluff]

How about

Sub CreateSht()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
   Ary = Ws2.Range("A1").CurrentRegion
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, -2).Value
      Next Cl
      For i = 1 To UBound(Ary)
         If .exists(Ary(i, 1)) Then Ary(i, 1) = .Item(Ary(i, 1))
      Next i
   End With
   Sheets("Sheet3").Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
   Sheets("Sheet3").Range("A1").Value = "ID"
End Sub

 

[AbuFawaz]

This is perfect if i run it on Sheet3, is there anyway to make the code create Sheet3 and show the result there ?
Yet thank you very much

 

[ethanscott]

How about

Sub CreateSht()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
   Ary = Ws2.Range("A1").CurrentRegion
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, -2).Value
      Next Cl
      For i = 1 To UBound(Ary)
         If .exists(Ary(i, 1)) Then Ary(i, 1) = .Item(Ary(i, 1))
      Next i
   End With
   Sheets("Sheet3").Range("A1").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
   Sheets("Sheet3").Range("A1").Value = "ID"
End Sub

Will the changes be made if I do it one either of the two sheets?
Is there a way to put it on autoupdate?

 

[Fluff]

@AbuFawaz
Try

Sub CreateSht()
   Dim Cl As Range
   Dim ary As Variant
   Dim i As Long
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("Sheet2")
   ary = Ws2.Range("A1").CurrentRegion
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, -2).Value
      Next Cl
      For i = 1 To UBound(ary)
         If .exists(ary(i, 1)) Then ary(i, 1) = .Item(ary(i, 1))
      Next i
   End With
   If Not Evaluate("isref(Sheet3!A1)") Then
      Sheets.Add(, Sheets(Sheets.Count)).Name = "Sheet3"
   End If
   Sheets("Sheet3").Range("A1").Resize(UBound(ary), UBound(ary, 2)).Value = ary
   Sheets("Sheet3").Range("A1").Value = "ID"
End Sub

@ethanscott
Please do not "Hijack" other peoples threads. You need to start a thread of your own.

 

[AbuFawaz]

Thank you very much, this is exactly what I need

 

[Fluff]

Glad to help & thanks for the feedback