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 .
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: