Why not just a sumproduct formula to get your sum?
=sumproduct((sheet1!a2:a22="joe")*(sheet1!b2:b22=1 )*sheet1!d2:d22)
or
=sumproduct((sheet1!a2:a22=a1)*(sheet1!b2:b22=1)*s heet1!d2:d22)

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
I am trying to create a macro that when run will take information from one
sheet and copy it into another. On the first sheet called Multi basis
report
there are 4 columns. (B) Security Id, (C) Security, (D) Realized G/L, and
(E)
Total Gain/Loss. I need to take the security Id #, the security name, and
it's realized gain/loss and paste it onto a sheet named 2006 Sched D
changes
for transfers. The issue I am having is that on my first sheet there are
multiple lines for each security Id instead of just one consolidated line.
I
have created subtotals for each security so there is a single line with
the
security Id and the total column summed up. Any help that can be offered
is
greatly appreciated.

Thanks in advance,
Brian Monaghan

One fairly easy way is to use datafilteradvanced filterunique values only
and copy that list to the other list and copy the formula down. A macro can
automate this process.

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
Thanks Don that worked great now I have my one line for each Security ID.
I
have one last question though is there a way that I can make it so that
every
time there is a change in the Security Id # that it copies down that
security
Id and the corresponding total figure to my second sheet. For instance I
want it to search through the first sheet see 2824100 and copy it to my
second sheet and put the total amount in column (D). Then find 02209S103
and
copy that on the next line down and put the total in column (D) and so on
until it finishes copying all of the security Id's onto the new sheet.


"Don Guillett" wrote:

Why not just a sumproduct formula to get your sum?
=sumproduct((sheet1!a2:a22="joe")*(sheet1!b2:b22=1 )*sheet1!d2:d22)
or
=sumproduct((sheet1!a2:a22=a1)*(sheet1!b2:b22=1)*s heet1!d2:d22)

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
I am trying to create a macro that when run will take information from
one
sheet and copy it into another. On the first sheet called Multi basis
report
there are 4 columns. (B) Security Id, (C) Security, (D) Realized G/L,
and
(E)
Total Gain/Loss. I need to take the security Id #, the security name,
and
it's realized gain/loss and paste it onto a sheet named 2006 Sched D
changes
for transfers. The issue I am having is that on my first sheet there
are
multiple lines for each security Id instead of just one consolidated
line.
I
have created subtotals for each security so there is a single line with
the
security Id and the total column summed up. Any help that can be
offered
is
greatly appreciated.

Thanks in advance,
Brian Monaghan

One way

Sub getunique()
With Sheets("sheet7")
..Range("A1:d12").AdvancedFilter Action:= _
xlFilterInPlace, Unique:=True
..Range("a2:d12").Copy Sheets("sheet2").Range("d12")
..ShowAllData
End With
End Sub

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
I must be missing something because after I use the advanced filter by
security ID unique record and try to copy it to the new worksheet it says
that you can only copy filtered data to the activesheet? I previously had
filtered the data though at least it looks like it since now there is only
one line for each security ID.

"Don Guillett" wrote:

One fairly easy way is to use datafilteradvanced filterunique values
only
and copy that list to the other list and copy the formula down. A macro
can
automate this process.

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
Thanks Don that worked great now I have my one line for each Security
ID.
I
have one last question though is there a way that I can make it so that
every
time there is a change in the Security Id # that it copies down that
security
Id and the corresponding total figure to my second sheet. For instance
I
want it to search through the first sheet see 2824100 and copy it to my
second sheet and put the total amount in column (D). Then find
02209S103
and
copy that on the next line down and put the total in column (D) and so
on
until it finishes copying all of the security Id's onto the new sheet.


"Don Guillett" wrote:

Why not just a sumproduct formula to get your sum?
=sumproduct((sheet1!a2:a22="joe")*(sheet1!b2:b22=1 )*sheet1!d2:d22)
or
=sumproduct((sheet1!a2:a22=a1)*(sheet1!b2:b22=1)*s heet1!d2:d22)

--
Don Guillett
SalesAid Software

"BrianMo" wrote in message
...
I am trying to create a macro that when run will take information
from
one
sheet and copy it into another. On the first sheet called Multi
basis
report
there are 4 columns. (B) Security Id, (C) Security, (D) Realized
G/L,
and
(E)
Total Gain/Loss. I need to take the security Id #, the security
name,
and
it's realized gain/loss and paste it onto a sheet named 2006 Sched D
changes
for transfers. The issue I am having is that on my first sheet
there
are
multiple lines for each security Id instead of just one consolidated
line.
I
have created subtotals for each security so there is a single line
with
the
security Id and the total column summed up. Any help that can be
offered
is
greatly appreciated.

Thanks in advance,
Brian Monaghan