XLA Routines: EE_ExtractRow

Nick's picture
Extracts a row of data and transposes it - useful for analysing records with many columns
Sub EE_ExtractRow(Optional SourceSht As Worksheet, Optional TargetSht As String, Optional RowToExtract As Long, Optional wb As Workbook, Optional blnTranspose As Boolean = True)
' takes the selected cell row as default
' copies and transposes onto a new sheet
'http://excelexperts.com/xla-routines-eeExtractRow    for updates on this sub routine

    If SourceSht Is Nothing Then
        Set SourceSht = ActiveSheet
    End If
 
    If wb Is Nothing Then
        Set wb = ActiveWorkbook
    End If
 
    If RowToExtract = 0 Then
        RowToExtract = Selection.Cells(1).Row
    End If
 
    If Not EE_SheetExists(TargetSht) Then
        TargetSht = "TempSht"
        If Not EE_SheetExists(TargetSht) Then
            Call EE_ReplaceSheet(TargetSht)
        End If
    End If
 
    Sheets(TargetSht).Move SourceSht
 
    If blnTranspose Then
        Call EE_Copy(SourceSht.Rows(1), Sheets(TargetSht).Cells(2, 1), True, True, blnTranspose)
        Call EE_Copy(SourceSht.Rows(RowToExtract), EE_TableFreeHeading(Sheets(TargetSht)).Offset(1), True, True, blnTranspose)
        Sheets(TargetSht).Cells(1) = "Heading"
        EE_TableFreeHeading(Sheets(TargetSht)) = "Value"
    End If
    Call EE_FinalFormatSheet(TargetSht)
End Sub