Ken,
Thanks for the response. The code works great! You da man! But I
have a question concerning the code, Currently, with your code, the
sheet works as expected (thanks) but I was thinking about making the
extended sheet and the breakdown sheet a little prettier and more
appealing to the reader.....If I was going to move the sold column down
some rows or wanted the copied data on the breakdown sheet in another
columns what lines of your code should I alter (I don't want to mess
your code up). And would your code support named ranges?

Ken Johnson wrote:
Hi Patrick,

Try out this macro on a COPY of your workbook...

Option Base 1
Public Sub SoldBooks()
Dim vaData As Variant
Dim vaSoldBooks() As String
Dim iLastRow As Long
Dim iLAstRowbd As Long

Dim iLastColumn As Integer
Dim iHeading As Integer
Dim iBookCount As Long
Dim iSoldCounter As Long
Dim iSoldColumn As Integer
With Worksheets("extended")
iLastColumn = _
.Cells(1, Range("1:1").Columns.Count).End(xlToLeft).Column
iLastRow = .Range("A" & .Range("A:A").Rows.Count).End(xlUp).Row
vaData = .Range(.Cells(1, 1), .Cells(iLastRow, iLastColumn))
End With

For iHeading = 1 To iLastColumn
If UCase(vaData(1, iHeading)) = "SOLD" Then
Let iSoldColumn = iHeading
Exit For
End If
Next iHeading
If iSoldColumn = 0 Then
MsgBox "Can't find 'Sold' Column"
Exit Sub
End If
For iBookCount = 1 To iLastRow
If UCase(vaData(iBookCount, iHeading)) = "YES" Then
Let iSoldCounter = iSoldCounter + 1
ReDim Preserve vaSoldBooks(iSoldCounter)
vaSoldBooks(iSoldCounter) = vaData(iBookCount, iHeading - 2)
End If
Next iBookCount
With Worksheets("breakdown")
iLAstRowbd = _
IIf(.Range("A" & .Range("A:A").Rows.Count).End(xlUp).Row < 3, 3, _
.Range("A" & .Range("A:A").Rows.Count).End(xlUp).Row)
.Range("A2:A" & iLAstRowbd).ClearContents
.Range("A2").Resize(UBound(vaSoldBooks), 1) _
.Value = WorksheetFunction.Transpose(vaSoldBooks)
End With

I didn't know where on the extended worksheet the sold column is so the
code does rely on the heading "sold" being somewhere in row 1. It is
not case sensitive so it can be any combination lower case and
capitals.

The code is set up to copy the sold books titles into column A of the
breakdown sheet, starting on row 2.

Every time you run the macro the old data in column A breakdown is
cleared before the copied sold titles are pasted.

The code can be run from any worksheet in the workbook, and also relies
on the sheets names being "breakdown" and "extended", again not case
sensitive.

The code could also be used as a WorksheetActivate Event Procedure so
that you wouldn't have to bother with manually running the macro. Excel
would run it automatically everytime you activated the breakdown sheet
when you click its worksheet tab.

Let me know how you go. I can make any needed changes.

Ken Johnson