Using VBA to Conditionally Select Rows in Excel

[WWUser]

I have an Excel workbook where data of a particular type is separated by "header" rows using colons to specify the data type:

:DataType1
FirstDataPoint
SecondDataPoint
ThirdDataPoint
:DataType2
FirstDataPoint
SecondDataPoint
ThirdDataPoint
:DataType3
FirstDataPoint
SecondDataPoint
ThirdDataPoint

The amount of data between the header rows is variable.

I would like to select all of the data between the first two header rows and copy/paste it to a new worksheet. Then copy all of the data between the second and third header rows and copy/paste it to a new worksheet, etc. (one worksheet for each data type).

I have tried many methods with filters, but my knowledge of VBA is limited.

Any thoughts/suggestions would be most appreciated.

WWUser

[kadghar]

Hi

It's not a great thing, but it certainly helps.
Just a few comments: try to write the name of the headers exactly as they apear in your wsheet, and add another one after the last column.

So if you had 9 headers, now you'll have 10, and the last one will only help the program to know when to stop copy and pasting.

Good Luck

Kad

Sub copypaste()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim numHdr As Integer
Dim Headers() As String
 
numHdr = 4
 
ReDim Headers(1 To numHdr)
Headers(1) = "header1"
Headers(2) = "header2"
Headers(3) = "header3"
Headers(4) = "EndOfFile"
 
i = 1
j = 1
 
While j <= numHdr
    If Worksheets(1).Cells(1, i).Value = Headers(j) Then
        j = j + 1
        Worksheets.Add after:=Worksheets(Worksheets.Count)
 
        k = 1
    End If
    Worksheets(1).Columns(i).Copy
    Worksheets(Worksheets.Count).Cells(1, k).Select
    Worksheets(Worksheets.Count).Paste
    i = i + 1
    k = k + 1
Wend
 
End Sub