Copy the extract between rows of 2 specific text and paste into another cells within same worksheet

[carolapl]

Hi

I am trying to extract the rows between the 2 lines that contains "LINE BREAK - START OF PIVOT TABLE ZONE" for column A:E and G:J.
The extract should be copied into L:O and Q:T.

I tried writing the below but do not seem to work

Sub LINEBREAK2()

Application.ScreenUpdating = False

'to extract the rows between line break and line break 2


With Worksheets("PnL FXOPT Piv Carol").Range("B:B")
Set c = .Find("LINE BREAK - START OF PIVOT TABLE ZONE", LookIn:=xlValues)
Set d = .Find("LINE BREAK 2 - START OF PIVOT TABLE ZONE", LookIn:=xlValues)
ActiveSheet.Range(Cells(c.Row, c.Column), Cells(d.Row, c.Column)).Select
Range.Copy Destination:=("L:O").Paste

End With


Application.ScreenUpdating = True

End Sub

I have attached what i have and expecting per the attached screenshot.



Thanks

 

[MikeVol]

I have attached what i have and expecting per the attached screenshot.

We can't see your screenshot, it's not in your post.

 

[carolapl]

We can't see your screenshot, it's not in your post.

Sorry reattaching

 

[MikeVol]

Hi @carolapl. Try next code

Option Explicit

Sub ExtractData()
    Dim i           As Long

    Dim ws          As Worksheet
    Set ws = ActiveSheet

    Dim startRows   As Object
    Set startRows = CreateObject("Scripting.Dictionary")

    Dim endRows     As Object
    Set endRows = CreateObject("Scripting.Dictionary")

    With Application
        .ScreenUpdating = False

        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

        Dim data    As Variant
        data = ws.Range("B1:J" & lastRow).Value

        For i = 1 To UBound(data)

            If data(i, 1) = "LINE BREAK - START OF PIVOT TABLE ZONE" Then
                If Not startRows.exists("B") Then startRows("B") = i Else endRows("B") = i
            End If

            If data(i, 6) = "LINE BREAK - START OF PIVOT TABLE ZONE" Then
                If Not startRows.exists("G") Then startRows("G") = i Else endRows("G") = i
            End If

        Next i

        If startRows.exists("B") And endRows.exists("B") Then ws.Range(ws.Cells(startRows("B"), "B"), ws.Cells(endRows("B"), "E")).Copy ws.Cells(5, "L")
        If startRows.exists("G") And endRows.exists("G") Then ws.Range(ws.Cells(startRows("G"), "G"), ws.Cells(endRows("G"), "J")).Copy ws.Cells(5, "Q")

        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

I hope I understood you correctly and was able to help you. Good luck.

 

[carolapl]

Hi @carolapl. Try next code

Option Explicit

Sub ExtractData()
    Dim i           As Long

    Dim ws          As Worksheet
    Set ws = ActiveSheet

    Dim startRows   As Object
    Set startRows = CreateObject("Scripting.Dictionary")

    Dim endRows     As Object
    Set endRows = CreateObject("Scripting.Dictionary")

    With Application
        .ScreenUpdating = False

        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

        Dim data    As Variant
        data = ws.Range("B1:J" & lastRow).Value

        For i = 1 To UBound(data)

            If data(i, 1) = "LINE BREAK - START OF PIVOT TABLE ZONE" Then
                If Not startRows.exists("B") Then startRows("B") = i Else endRows("B") = i
            End If

            If data(i, 6) = "LINE BREAK - START OF PIVOT TABLE ZONE" Then
                If Not startRows.exists("G") Then startRows("G") = i Else endRows("G") = i
            End If

        Next i

        If startRows.exists("B") And endRows.exists("B") Then ws.Range(ws.Cells(startRows("B"), "B"), ws.Cells(endRows("B"), "E")).Copy ws.Cells(5, "L")
        If startRows.exists("G") And endRows.exists("G") Then ws.Range(ws.Cells(startRows("G"), "G"), ws.Cells(endRows("G"), "J")).Copy ws.Cells(5, "Q")

        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

I hope I understood you correctly and was able to help you. Good luck.

Hi Mike

Thank you for the above.

Can you advise what does data(i,1) and data(i,6) mean?

For i = 1 To UBound(data)

If data(i, 1) = "LINE BREAK - START OF PIVOT TABLE ZONE" Then
If Not startRows.Exists("B") Then startRows("B") = i Else endRows("B") = i
End If

If data(i, 6) = "LINE BREAK - START OF PIVOT TABLE ZONE" Then
If Not startRows.Exists("K") Then startRows("K") = i Else endRows("K") = i
End If

Thanks

 

[MikeVol]

Can you advise what does data(i,1) and data(i,6) mean?

The data(i,1) and data(i,6) lines operate on the data array, which contains the data from the range B1:J (data = ws.Range("B1:J" & lastRow).Value) as a two-dimensional array.
If data(i, 1) = "LINE BREAK - START OF PIVOT TABLE ZONE" checks whether column B in row i contains the search text.
If data(i, 6) = "LINE BREAK - START OF PIVOT TABLE ZONE" does the same for column G.

 

[Fuji]

Different method.
simpy

Sub test()
    Dim e, x
    With Sheets("PnL FXOPT Piv Carol")
        For Each e In [{"b","g"}]
            x = Filter(.Evaluate(Replace("transpose(if(#=""LINE BREAK - START OF PIVOT TABLE ZO""," & _
                "row(#)))", "#", e & "1:" & e & .Cells.SpecialCells(11).Row)), False, 0)
            If UBound(x) > 0 Then
                With .Cells(x(0) - 2, e).Resize(x(1) - x(0) + 3, 4)
                    .Copy
                    .Offset(, 10).PasteSpecial xlPasteColumnWidths
                    .Copy .Offset(, 10)
                End With
            End If
        Next
        Application.Goto .Range("b" & x(0))
    End With
End Sub

 

[carolapl]

thanks both. both works

 

[Fuji]

You are welcome and thanks for the feedback.

 

[MikeVol]

thanks both. both works

Hi @carolapl and @Fuji. How do both options work? The code provided by Fuji does not work for me. See the screenshots and the example file for proof that the code does not work.