Sub CONVERTROWSTOCOL_Oeldere_revisted_new()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

'set the data
                 

Set mr = Sheets("sheet1")                                  'this is the name of the source sheet
 
Set ms = Sheets("Output")                                       'this is the name of the destiny sheet

col = 2
'End set the data

    With ms
     .UsedRange.ClearContents
     .Range("A1:B1").Value = Array("Mat", "value")
    End With
    
    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
    
    
    With mr
          rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
          For i = 2 To rsht1
                Do While .Cells(1, col).Value <> "" 'And .Cells(I, col).Value <> ""
                rsht2 = rsht2 + 1
               
                ms.Range("A" & rsht2).Value = .Range("A" & i).Value
                
               
                ms.Range("B" & rsht2).Value = .Cells(i, col).Value
         
                col = col + 1
            Loop
            col = 2
        Next
    End With
    
  With ms
  
  
  
  
    .Columns("A:Z").EntireColumn.AutoFit
    
    End With
    
End Sub

Boa noite Matheus

Como você é novato, para facilitar a tua participação no fórum, sugiro tomar conhecimento do conteúdo dos links abaixo:
viewtopic.php?f=7&t=203
viewtopic.php?f=7&t=7903
viewtopic.php?f=7&t=3841
viewtopic.php?f=7&t=16757
viewtopic.php?f=7&t=12600
viewtopic.php?f=7&t=3371

Você não clicou na mãozinha, para agradecer aos colaboradores que te ajudaram nos tópicos criados.

[]s

Patropi - Moderador