Thread
:
Selectively Moving Data to a Summary Sheet
View Single Post
#
14
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Selectively Moving Data to a Summary Sheet
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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett