Extracting Comments

Something like this.

Sub ExtractComments()
'
' This sub will extract
' comments from code
'
    Dim strCode As String
    Dim strComments As String
    Dim lngRow As Long
    Dim vntLine As Variant
        
    ' access active project
    With Application.VBE.ActiveVBProject.VBComponents.Item("Module1").CodeModule
        ' grab all lines of code
        strCode = .Lines(1, .CountOfLines)
    End With
    
    For Each vntLine In Split(strCode, vbCrLf)
        ' locate comment line
        If Left(Trim(vntLine), 1) = "'" Then
            lngRow = lngRow + 1
            ' output to worksheet
            ActiveSheet.Cells(lngRow, 1) = vntLine
        End If
    Next
    
End Sub