find and add values in different worksheets
 

Hi,

I need to do the following:
ws1 and 2
column A has item number, column B has quantity.

Same format, different data.
If both worksheets have the same item number, then add the quantities
together and ammend worksheet 1, if data only exists in worksheet 2 and
not in 1, then add row to ws1. If data exists in worksheet 1 and not
in 2, then leave alone. WS1 to be the master.

Thanks in advance

find and add values in different worksheets
 

Hi Tom,

Thanks for the speedy reply, now how do I enter that into Excel?

Regards
Owen

Tom Ogilvy wrote:
Sub Consolidate Data
dim rng as Range
Dim lastrow as Long
Dim i as Long
with worksheets("ws2")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))
End with
With worksheets("ws1")
rng.Resize(,2).copy Destination:= _
.Cells(rows.count,1).End(xlup)(2)
lastrow = .cells(rows.count,1).End(xlup)
.Range("A1").currentRegion.Sort Key1:=.Range("A1"), _
Headers:=xlYes
for i = lastrow to 3 step -1
if .cells(i,1) = .cells(i-1,1) then
.cells(i-1,2) = .cells(i-1,2) + .cells(i,2)
.rows(i).Delete
end if
Next
End with
End Sub

--
Regards,
Tom Ogilvy


"splat" wrote:

Hi,

I need to do the following:
ws1 and 2
column A has item number, column B has quantity.

Same format, different data.
If both worksheets have the same item number, then add the quantities
together and ammend worksheet 1, if data only exists in worksheet 2 and
not in 1, then add row to ws1. If data exists in worksheet 1 and not
in 2, then leave alone. WS1 to be the master.

Thanks in advance

find and add values in different worksheets
 

Hi Tom,

Thanks for the speedy reply, now how do I enter that into Excel?

Regards
Owen

Tom Ogilvy wrote:
Sub Consolidate Data
dim rng as Range
Dim lastrow as Long
Dim i as Long
with worksheets("ws2")
set rng = .range(.cells(2,1),.cells(2,1).End(xldown))
End with
With worksheets("ws1")
rng.Resize(,2).copy Destination:= _
.Cells(rows.count,1).End(xlup)(2)
lastrow = .cells(rows.count,1).End(xlup)
.Range("A1").currentRegion.Sort Key1:=.Range("A1"), _
Headers:=xlYes
for i = lastrow to 3 step -1
if .cells(i,1) = .cells(i-1,1) then
.cells(i-1,2) = .cells(i-1,2) + .cells(i,2)
.rows(i).Delete
end if
Next
End with
End Sub

--
Regards,
Tom Ogilvy


"splat" wrote:

Hi,

I need to do the following:
ws1 and 2
column A has item number, column B has quantity.

Same format, different data.
If both worksheets have the same item number, then add the quantities
together and ammend worksheet 1, if data only exists in worksheet 2 and
not in 1, then add row to ws1. If data exists in worksheet 1 and not
in 2, then leave alone. WS1 to be the master.

Thanks in advance

find and add values in different worksheets
 

O.K., I got into excel, it gives me the following:

Compile Error:
Expected: End Of Statement

Also, the word Data in (Sub Compile Data) highlights to Blue

find and add values in different worksheets
 

Now it wants me to debug the line

With Worksheets("ws2")

find and add values in different worksheets
 

Thanks Tom, it works great, you have no idea how much work you saved me
here!