Hi Don,

Thank you so much for your help. Your code is fantastic. You do so much with
such few lines of code.

It turns out I am struggling a bit trying to get it to cycle through all the
sheets with €œTicker€� in A1. I will keep trying but your help would be very
appreciated.

I made a mistake with writing up the desired outcome by saying that I wanted
to skip blocks of data that dont have €œnet€� dollar amounts in column P. I
now realize that in those instances I would really like the macro to return
the: symbol, the start date as the top date of the column for its block, the
end date as the bottom date of its block and the dollar net amount to be
blank.

Thanks so much.

--
John Yab


"Don Guillett" wrote:

Sub GetDataSAS() 'insert a row at the top of the sheet
Application.ScreenUpdating = False

Dim r As Long
Dim lr As Long
Dim c As Range
r = 2
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets(1).Range("p1:p" & lr)
Set c = .Find(What:="Net", After:=Range("p1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
firstvaluerow = Evaluate("=MATCH(1,--(P" & c.Row + 1 & ":P" & lr &
"<""""),0)") + c.Row
If LCase(Cells(firstvaluerow, "P")) < "net" Then
With Sheets("summary")
..Cells(r, "e") = Cells(c.Row + 1, 1) 'symbol
..Cells(r, "f") = Cells(c.Row + 1, 2) 'startdate
..Cells(r, "g") = Cells(firstvaluerow, "B") 'enddate
..Cells(r, "h") = Cells(firstvaluerow, "P") 'endvalue
End With
r = r + 1
End If

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Application.ScreenUpdating = True
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John Yab" wrote in message
...
Would someone please help me with a macro? I have data on a sheet that I
am
trying to selectively move to a summary sheet; below is an example:

Revenue Net


$1,296.00 $24.00

Revenue Net

$964.00 ($28.00)


Revenue Net
$416.00 ($40.00)
$416.00 ($40.00)
$416.00 ($40.00)

There are blocks of data on a sheet. Each block has a different number of
rows. Each block is seperated by one blank row.
In column P is the heading "Net" in each block. Under "Net" can be blanks
or
a dollar amount or the blank between blocks of data or a new heading of
"Net"
for a new block of data. I am trying to move the dollar amount to a
summary
sheet. Sometimes there is more than one dollar amount in each block... I
only
want the first instance of the dollar amount then I need to skip to the
next
block and get the first instance of the dollar amount in that next block.
Sometimes there is no dollar amount and then I would have to skip to the
next
block of data. When/if I find the dollar amount I need to copy and paste
it
to the summary sheet and also copy and past the values in that same row
from
column A (an ID) and column B (the end date). The value 2nd from the top
of
the column B in that block of data is the "start" date. I need to capture
the
start date too and move it to the summary sheet. In summary the macro
would
grab 4 bits of data and move it to the summary sheet and then move on to
the
next block of data a grab and move 4 bits of data from that block, etc.
I have been working hard with loops and cases and if's for a week and
can't
quite get it so any help is very appreciated with big thanks.

--
John Yab


.