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

 

[Fluff]

Hi & welcome to MrExcel.
Any reason you want a macro, as this can be done with a formula?

 

[anpak]

Hello fluff,

how can I do that with formulas?

I asked for a macro because the table "input" could have different size (it becomes from an external report).

Thank you

 

[Fluff]

Ok, will it always be 5 columns wide with a varying number of rows?

 

[anpak]

The table "Input" can have maximum 50 colums and variable rows

 

[Fluff]

Is it an actual table, or just a range of cells?
Also does it have a header row?

 

[anpak]

No it is not formatted as table, it is just a range of cells

 

[Fluff]

And a header row?

 

[anpak]

ops sorry, the header row is present, but we don't have to consider it.

 

[Fluff]

Ok, how about

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
   Sheets("Sheet2").Range("A2").Resize(nr, 2).Value = Nary
End Sub