Create a new table starting from another one

[anpak]

Hello,

I'm new on this forum. Below my first question

I would like to create a macro in order to obtain the table named "Output" starting from the table named "Input". Please see the image below.

Is it possible?

Thank you in advance

 

[anpak]

Fluff thank you it works!!!

Please can you add those "features":

1. If the cell in Input table is blank, the vba script have not to create the row in the output table (as in the image, Role 1 Tool 4)
2. The output table must have the header: Role (A1) and Tool (A2).

Thank you in advance

 

[Fluff]

This will add the header

Sub anpak()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   With Sheets("Sheet1")
      c = .Cells(1, Columns.Count).End(xlToLeft).Column
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, c).Value2
   End With
   ReDim Nary(1 To UBound(Ary) * c, 1 To 2)
   
   For r = 1 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, c)
         End If
      Next c
   Next r
   With Sheets("Sheet2")
      .Range("A1:B1").Value = Array("Role", "Tool")
      .Range("A2").Resize(nr, 2).Value = Nary
   End With
End Sub

Part 1 is already done.
If you are getting "blank"cells in the output then your input cells are not empty.

 

[anpak]

Ok Fluff, now the vba script is perfect.

Thank you so much for your support!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[anpak]

Sorry Fluff, one more feature.

Is it possible to start writing the output table not always from "A2", but starting from the first blank cell in column A?

 

[Fluff]

Yup, you can do that like

      .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, 2).Value = Nary

 

[anpak]

Thank you again, it works!!! You're VBA black belt

 

[Fluff]

Glad to help & thanks for the feedback.

 

[anpak]

Hello @Fluff ,

I would like a script that just transposes the first line in a sheet (excluded the first two cells-A1,A2) in the first column of another sheet, after last blank cell.

Could you help me please?

Regards,
Antonio

 

[Fluff]

As that's a totally different question, it needs a new thread. Thanks