Create new sheet

[AbuFawaz]

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

Sheet1

ID	Name	Code
A001	A	C1
A002	B	C2
A003	C	C3
A004	D	C4

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet2

Code	ID_2	Data
C1	B001	2
C1	B002	3
C1	B003	4
C1	B004	3
C1	B005	2
C2	B006	1
C2	B007	2
C3	B008	2
C3	B009	2
C3	B010	5
C4	B011	4
C4	B012	6
C4	B013	2
C4	B014	1
C4	B015	2
C4	B016	3
C4	B017	5
C4	B018	5

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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