Hi,

Can anyone tell me how to automate this in excel through a macro??

Let me explain the whole thing to you. I have 2 excel workbooks with name as follows:
1) rawdata.xls: it contains a rawdata in this form

Page: 7
Author: sullib4 Subject: Note Date: 4/13/2010 11:38:12 PM
Break line here so that page number appears on a line with text and leaders
Page: 17
Author: sullib4 Subject: Comment on Text Date: 4/13/2010 11:42:11 PM
normal, no italic
Author: sullib4 Subject: Replacement Text Date: 4/13/2010 11:41:57 PM
systems
Page: 19
Author: sullib4 Subject: Cross-Out Date: 4/13/2010 11:43:01 PM
Author: sullib4 Subject: Inserted Text Date: 4/13/2010 11:43:22 PM
insert a space here
Page: 22
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:44:35 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:44:30 PM
Author: sullib4 Subject: Inserted Text Date: 4/14/2010 12:35:21 AM
the
Author: sullib4 Subject: Inserted Text Date: 4/14/2010 12:35:47 AM
insert a registered trademark here
Page: 23
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:48:48 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:48:40 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:49:55 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:49:42 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:49:50 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:51:41 PM
Author: sullib4 Subject: Replacement Text Date: 4/13/2010 11:52:38 PM
Model and quantity
Author: sullib4 Subject: Inserted Text Date: 4/13/2010 11:55:55 PM
Optional
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:50:59 PM
Author: sullib4 Subject: Pencil Date: 4/13/2010 11:56:20 PM

2) Table.xls: Its has a table with the following 4 headings and columns:

Page Number
Type of Comment
Commenter
Comment Description

What exactly I want this macro to do is:

1) It should copy the page number( from rawdata.xls) and paste it to the 'PAGE NUMBER' column ( in Table.xls).
2) Next, It should copy all the rows that starts with the word 'Author' under above Page number heading (from rawdata.xls) and paste it to the 'TYPE OF COMMENT' column ( in Table.xls).
3) Next, It should copy all the rows that does not starts with the word 'Author' under above Page number heading (from rawdata.xls) and paste it to the 'COMMENT DISCRIPTION' column ( in Table.xls).
4) It should repeat the same for each and every Page Number.