I have received an excel document as a 150 page manual. Each page is setup
as a proforma, containing the same headings, tables etc. although the tables
have different amounts of rows. I want to make this data more user friendly,
rather than havingto troll through the pages to find the required
information.

I have created a new worksheet with headings and, in order to copy the
information from the titles, i.e.
A1 B1
Element Windows
Feature Timber Frames
I think it needs to do the following:

For each cell in worksheet
If cell value="Element"
Copy next cell (right of) to sheet 2, column 1, row 2

Next cell -> if cell value -"Element" copy to sheet 2, column 1, row 3 and
so on.


Regards
Dylan DAwson

John

Much appreciated, your code did exactly what I wanted and I have rated your
post as helpful, thank you.
I have now populated the first theree columns on Sheet2 with my data.

My next task is to copy over the data from the tables in sheet 1. The first
column in my table has the heading "Attribute". It can have a from 3 to 12
row entries. Can I use your code to look for the heading "attribute" and
then create a list from the contents of the rows below (until it reaches a
blank cell perhaps) and paste the list of entries into the relevant cell in
Sheet 2, column D?

Regards
Dylan Dawson

"John Bundy" <(E-Mail Removed)> wrote in message
news:104B5426-04F0-4C33-9FA8-(E-Mail Removed)...
Made as a button, change myRow or array as needed:

Private Sub CommandButton1_Click()

Dim myRow As Integer
Dim lastRow As Integer
Dim iArray(10000, 2) As Variant
Dim iIndex As Integer

iIndex = 1
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 1

For i = 1 To lastRow
If Cells(myRow, 1) = "Element" Then _
'populate array
iArray(iIndex, 1) = Cells(myRow, 1)
iArray(iIndex, 2) = Cells(myRow, 2)
iIndex = iIndex + 1
End If
myRow = myRow + 1
Next
iIndex = 1
myRow = 2
Sheets("sheet2").Select

Do Until iArray(iIndex, 2) = ""
Sheets("sheet2").Cells(myRow, 1) = iArray(iIndex, 2)
iIndex = iIndex + 1
myRow = myRow + 1
Loop

End Sub

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"dd" wrote:

> I have received an excel document as a 150 page manual. Each page is setup
> as a proforma, containing the same headings, tables etc. although the
> tables
> have different amounts of rows. I want to make this data more user
> friendly,
> rather than havingto troll through the pages to find the required
> information.
>
> I have created a new worksheet with headings and, in order to copy the
> information from the titles, i.e.
> A1 B1
> Element Windows
> Feature Timber Frames
> I think it needs to do the following:
>
> For each cell in worksheet
> If cell value="Element"
> Copy next cell (right of) to sheet 2, column 1, row 2
>
> Next cell -> if cell value -"Element" copy to sheet 2, column 1, row 3 and
> so on.
>
>
> Regards
> Dylan DAwson
>
>
>