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" <JohnBundy@discussions.microsoft.com> wrote in message
news:104B5426-04F0-4C33-9FA8-2F939BF61B62@microsoft.com...
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
>
>
>