vba code to find a predetermined range, copy and transpose

I'm working on a excel document with multiple seperate data, all in a single column (A1 to A10160).

All the data begins in a cell with the text NC/xx/xxxx/x (x being variable) and ending in a cell containing different dates but the cell above it always has the text "Start Date". Some data covers 49 cells others cover 51 cells so it's not contained in a fixed number of cells in the column.

I need to copy the range from NC/xx/xxxx/x to Start Date plus one for each data "set", transpose it and paste all the data in the column in a new sheet.

Really haven't found anything useful so far but I am fumbling with this one:

Sub Find()  Dim Search, End, Start, i As Integer, j As Integer, L      Search = Cells(1, 1)      End = Cells(2, 1)      For i = 1 To 10160          If Left(Cells(i, 1), 3) = Search Then              Start = i - 0          End If      Next i      For j = 1 To 10160      If Cells(j, 1) = End Then              L = j + 1          End If              Sheet4.Select              Range(Cells(Start, 1), Cells(L + 2, 1)).Select              Selection.Copy              Sheet4.Range("BB23").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True          Application.CutCopyMode = False      End      Next j        End Sub  

Would really appreciate any help I can get!

Thanks!

https://stackoverflow.com/questions/65850360/vba-code-to-find-a-predetermined-range-copy-and-transpose January 23, 2021 at 01:56AM