Guru do Excel e Power BI

Option Explicit

Sub AleVBA_301()
   Dim origSh As Worksheet
   Dim resSh As Worksheet
   Dim lastRow As Long, r As Long
   Dim lastCol As Integer, c As Integer
   Dim destRow As Long, myText
   
   
   With ThisWorkbook
      Set origSh = .Sheets("Plan1")
      Set resSh = .Sheets("Plan2")
   End With
      
   lastRow = origSh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   lastCol = origSh.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

   destRow = 1
   resSh.Rows("2:" & Rows.Count).ClearContents
   
   For r = 2 To lastRow
      For c = 2 To lastCol
         If Trim(origSh.Cells(r, c)) <> "" Then
            For Each myText In Split(Trim(origSh.Cells(r, c)), " ")
               destRow = destRow + 1
               
               resSh.Cells(destRow, 1) = origSh.Cells(r, 1)
               resSh.Cells(destRow, 2) = origSh.Cells(1, c)
               resSh.Cells(destRow, 3) = myText
            Next
         End If
      Next c
   Next r
End Sub