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
1	header1	header2
2	a	1
3	w2	5
4	f	2
5	g	34
6	h	5
7	*	6
8	j	7
9	k	8
10	*	*
11	*	*
12	*	*
13	*	*
14	*	*
15	*	*
16	header1	header2
17	w	3
18	d	5
19	f	7
20	r	8
21	t	9
22	g	54
23	y	3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</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