I have following data wherein I want copy data between all "sr" and "extra" to separate sheet . I have following vba which copies data from first meeting of find conditions ie., row after sr and one row before extra, which is perfect. But same worksheet I have more data which succeed with "sr" and proceeds with extra which I am not not able to copy. again, I want add column in the data sheet and for first meeting sr & extra combination I want add "1" in new column and 2 for next and so on Thereafter I want to transpose them as shown in output . I have attached sample data and output image Kindly help me out .
VBA Code:
Sub loop_through_all_worksheetsnnN12()
Sheets("Sheet1").Select
Dim ws As Worksheet
Dim starting_ws As Worksheet
Dim wsResults As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "matches"
ws.Activate
With ws
With Range("d" & Columns("c").Find(What:="SRg", LookAt:=xlPart, MatchCase:=False).Row + 1 & _
":d" & Columns("c").Find(What:="Extra", LookAt:=xlPart, MatchCase:=False).Row - 1)
Sheets("Scoresheet").Activate
Set wsResults = ActiveSheet
.Offset(0).Resize(.Rows.Count).EntireRow.Copy Destination:=wsResults.Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
End With
End With
End If
Next
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: