XLA routines: EE_ExtractRow

Nick's picture
EE_ExtractRow extracts a row from a data table, and puts it on a new sheet either transposed, or the same. - useful for extracting a record and analysing
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 paste transpose onto a new sheet
' copies row and header onto sheet specified
'http://excelexperts.com/xla-routines-eeExtractRow    for updates on this sub

    If SourceSht Is Nothing Then
        Set SourceSht = ActiveSheet
    End If
 
    If IsMissing(wb) 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"
    End If
 
    Call EE_ReplaceSheet(TargetSht)
    If blnTranspose Then
        Call EE_Copy(SourceSht.Rows(1), Sheets(TargetSht).Cells(2, 1), True, True, blnTranspose)
        Call EE_Copy(SourceSht.Rows(RowToExtract), Sheets(TargetSht).Cells(2, 2), True, True, blnTranspose)
        Sheets(TargetSht).Cells(1) = "HEADING"
        Sheets(TargetSht).Cells(1, 2) = "VALUE"
    Else
        Call EE_Copy(SourceSht.Rows(1), Sheets(TargetSht).Cells(1, 1), True, True, blnTranspose)
        Call EE_Copy(SourceSht.Rows(RowToExtract), Sheets(TargetSht).Cells(2, 1), True, True, blnTranspose)
    End If
    Call EE_FinalFormatSheet(TargetSht)
 
End Sub