Thanks Don,

Yes yours works perfectly too.
I have run it many times and will now study it to learn from it.
Hopefully one day I will be able to help by providing answers like you have
kindly done for me.
Thank you very much for working on this for me.
--
John Yab


"Don Guillett" wrote:

OOPs NOT necessary to insert a row at the top

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
This also works

Sub GetDataAllSheetsSAS() 'insert a row at the top of the sheet
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim firstaddress
Dim r As Long
Dim lr As Long
Dim i As Long
Dim c As Range

With Sheets("Summary")
lr = .Cells(Rows.Count, 1).End(xlUp).Row
.Rows(2).Resize(lr).Delete
End With

r = 2
For Each ws In Worksheets
If ws.Name < "Summary" And ws.Range("a1") = "Ticker" Then

lr = ws.Cells(Rows.Count, "a").End(xlUp).Row
With ws.Range("p1:p" & lr)
Set c = .Find(What:="Net", after:=ws.Range("p" & lr), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
firstaddress = c.Address
Do

With Sheets("summary")
.Cells(r, "e") = ws.Name
.Cells(r, "a") = ws.Cells(c.Row + 1, 1) 'symbol
.Cells(r, "b") = ws.Cells(c.Row + 1, 2) 'startdate
.Cells(r, "c") = ws.Cells(c.Row + 1, 2).End(xlDown) 'enddate
For i = c.Row + 1 To ws.Cells(Rows.Count, 1).End(xlUp).Row

If Len(ws.Cells(i, "p")) 0 Then
If ws.Cells(i, "p") = "Net" Then
.Cells(r, "d") = 0 'end value
Else
.Cells(r, "d") = ws.Cells(i, "p") 'end value
End If

Exit For
End If
Next i

End With
r = r + 1

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
End If
'MsgBox ws.Name
Next ws
Application.ScreenUpdating = True
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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




.