Separating Data into Different Sheets

[neodjandre]

Hello all,

I am using a web query to pull some data with the following structure:

Row132: Header 1

Rows133-200: Data set 1

Row201: Header 2

Rows201-300: Data set 2


I would like to separate the data sets into 2 different sheets.

The problems i am facing:

- Data is dynamic - Header 1 and Header 2 positions can vary

- Number of rows of data sets can vary

The only constant is that I always know the names of Header 1 and Header 2.

Based on this information is there a formula I can use to separate the data to two different sheets?

many thanks
Andy

 

[venkat1926]

suppose sample data is like this

Sheet1

*	A	B
header1	header2
a
w2
f
g
h
*
j
k
*	*
*	*
*	*
*	*
*	*
*	*
header1	header2
w
d
f
r
t
g
y

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]34[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"]54[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: right"]3[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


try this macro(moodify)

Sub test()
Worksheets("sheet2").Cells.Clear
Worksheets("sheet3").Cells.Clear
Worksheets("sheet1").Activate


Dim j As Integer, k As Integer, lastrow As Integer
j = Cells.Find(what:="header1", lookat:=xlWhole, after:=Cells(Rows.Count, 1)).Row
'MsgBox j
k = Cells.Find(what:="header1", lookat:=xlWhole, after:=Cells(j, 1)).Row
'MsgBox k
lastrow = Cells(k, 1).End(xlUp).Row


'MsgBox lastrow
Range(Cells(j, 1), Cells(lastrow, 1)).EntireRow.Copy Worksheets("sheet2").Range("A1")
lastrow = Cells(k, 1).End(xlDown).Row
Range(Cells(k, 1), Cells(lastrow, 1)).EntireRow.Copy Worksheets("sheet3").Range("A1")




End SubSub test()
Worksheets("sheet2").Cells.Clear
Worksheets("sheet3").Cells.Clear
Worksheets("sheet1").Activate


Dim j As Integer, k As Integer, lastrow As Integer
j = Cells.Find(what:="header1", lookat:=xlWhole, after:=Cells(Rows.Count, 1)).Row
'MsgBox j
k = Cells.Find(what:="header1", lookat:=xlWhole, after:=Cells(j, 1)).Row
'MsgBox k
lastrow = Cells(k, 1).End(xlUp).Row


'MsgBox lastrow
Range(Cells(j, 1), Cells(lastrow, 1)).EntireRow.Copy Worksheets("sheet2").Range("A1")
lastrow = Cells(k, 1).End(xlDown).Row
Range(Cells(k, 1), Cells(lastrow, 1)).EntireRow.Copy Worksheets("sheet3").Range("A1")




End Sub