automatically extract data from separate cells with variable rows inbetween when criteria met

[mingandmong]

Hi
Im using excell 2016

i wish to exctract rows of certain cells of data into a new sheet when the two criteria are met in column "B"
my sheet has a 5000 rows and a just require a summary sheet

search column "B" 1st criteria = NameB:9) extract cell E:9 D:9 & C:9 and place in a new sheet A2:C2
Then search Column "B" 2nd criteria = Daily (B:18) extract cell C:20, E:20, G:20, I:20, K:20, M:20, O:20 & Q:20

and place in a new sheet D2:K2

repeat for the remaining sheet in a loop

i have place links below.. many thanks in advance

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://ibb.co/ssd6gw7
https://ibb.co/MR6qQH7

http:// [/FONT]​

 

[Fluff]

How about

Sub mingandmong()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Sheet2")
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Replace "Name:", "=xxx", xlWhole, , False, , False, False
      With .SpecialCells(xlFormulas, xlErrors)
         .Offset(, 3).Copy Ws.Range("A2")
         .Offset(, 2).Copy Ws.Range("B2")
         .Offset(, 1).Copy Ws.Range("C2")
      End With
      .Replace "=xxx", "Name:", xlWhole, , False, , False, False
      .Replace "Daily", "=xxx", xlWhole, , False, , False, False
      With .SpecialCells(xlFormulas, xlErrors)
         Union(.Offset(2, 1), .Offset(2, 3), .Offset(2, 5), .Offset(2, 7), .Offset(2, 9), .Offset(2, 11), .Offset(2, 13), .Offset(2, 15)).Copy Ws.Range("D2")
      End With
      .Replace "=xxx", "Daily", xlWhole, , False, , False, False
   End With
End Sub

 

[mingandmong]

Hi Fluff

i dont know how you do it, but im total impressed yet again with your expertise, and resolved first time around.. not the first time you have helped me out
i have noticed just a little querk with my data and has nothing to do with your formula/macro
the name(s) in coulmn "B1" end in a comma so my current dats is joe, and Elena, would be joe and Elena if that were possible and easy to add if not i can use another method to clean up

i have another issue for another sheet in the near future i hope your the one to resolve it for me.. thanx again for all your hard work

 

[Fluff]

Try adding this before the End Sub line

   Ws.Range("C:C").Replace ",", "", xlPart, , False, , False, False

I assume you mean col C not B, as that's where Joe & Elana end up.

 

[mingandmong]

hi Fluff
i did mean "B" on sheet1 and yes "C" on sheet 2 .. you have resolved all my issues.. thankyou once again

 

[Fluff]

You're welcome & thanks for the feedback