Macro - Copy Table Column & Row Names When Codition Is Met

[steven.holloway]

May Jun Jul
Apple No Yes No
Pear Yes No Yes
Orange Yes Yes No

1) I would like to use a macro to go through the above table (using I think
a range, a loop and a if) and for every "Yes" I want to copy out the "Fruit
Name" & " the "Month Name" into seperate columns in another worksheet
(example below)
Apple Jun
Pear May
Pear Jul
Orange May
Orange Jun

2) My actual data table is larger and may expanded in both directions. If I
define a name for the range in Excel, can I use this named range in a macro?

Many thanks in advance for any help possible
Steve

 

[Joel]

You can use a named range from worksheet
Replace FindRange below with Range("WorksheetName"). You need the double
quotes around the name.

The code below automatically finds size of table if table. change StartCell
if the table starts in another location.


Sub getfruit()
StartCell = "A1"

With Sheets("Sheet1")
LastRow = .Range(StartCell).Offset(1, 0).End(xlDown).Row
LastColumn = .Range(StartCell).Offset(0, 1).End(xlToRight).Column
Set FindRange = .Range(.Range(StartCell).Offset(1, 1), _
.Cells(LastRow, LastColumn))
End With
RowCount = 1
For Each cell In FindRange
If cell = "Yes" Then
Fruit = Cells(cell.Row, "A")
FMonth = Cells(1, cell.Column)
With Sheets("Sheet2")
.Range("A" & RowCount) = Fruit
.Range("B" & RowCount) = FMonth
RowCount = RowCount + 1
End With
End If

Next cell

End Sub

 

[steven.holloway]

Thanks Joel, this was exactly what I requested, I can now adjust to my needs.

Thanks again and have a great Xmas/New Year.
Steve