VBA to create a table range based on a list

[thedeadzeds]

Not sure if this is even possible but for every id in the list in worksheet 1, can I copy the data from workbook 2 cells A2 to V10 and create this for ID. Example below:

WORKSHEET 1

ID	Name
1585	Dave
1938	Jim
2033	Steve
974	Sarah
2323	James
3070	Helen
3070	Helen

WORKSHEET 2

ID

Name

Earning/Code

Amount 1

Amount 2

Amount 3

Amount 4

Deduction/Code

Deduction/Amount

Deduction/Quantity

Deduction/Percentage

Deduction/Additional

Contribution/Code

Contribution/Amount

Contribution/Quantity

Contribution/Percentage

Contribution/Additional

Overrides/Cost Centre

Overrides/GL Business Code

Overrides/Transaction Date

Overrides/Comment

Overrides/Target Tax Period

1585​

Dave

Fuel

1585​

Dave

Coms1

1585​

Dave

Coms2

1585​

Dave

Coms3

1585​

Dave

Coms4

1585​

Dave

AHP

1585​

Dave

Bonus

1585​

Dave

Motab

1585​

Dave

Dedt

1938​

Jim

Fuel

1938​

Jim

Coms1

1938​

Jim

Coms2

1938​

Jim

Coms3

1938​

Jim

Coms4

1938​

Jim

AHP

1938​

Jim

Bonus

1938​

Jim

Motab

1938​

Jim

Dedt

 

[Peter_SSs]

Assuming Sheet2 already exists with headings, try this with a copy of your workbook.

Sub ws2()
  Dim a As Variant, b As Variant, ec As Variant
  Dim i As Long, j As Long, k As Long
  
  ec = Split("Fuel|Coms1|Coms2|Coms3|Coms4|AHP|Bonus|Motab|", "|")
  
  With Sheets("Sheet1")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  ReDim b(1 To UBound(a) * 9, 1 To 8)
  For i = 1 To UBound(a)
    For j = 0 To 8
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = ec(j)
    Next j
    b(k, 8) = "Dedt"
  Next i
  Sheets("Sheet2").Range("A2:H2").Resize(k).Value = b
End Sub