Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and add values in different worksheets
Now it wants me to debug the line
With Worksheets("ws2") |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
find and add values in different worksheets
Thanks Tom, it works great, you have no idea how much work you saved me
here! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
How do I find and replace null values in Excel 2002 worksheets? | Excel Worksheet Functions | |||
Find ALL WorkSheets according to 2 ComboBox Values.... Help with Code | Excel Programming | |||
find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
Search/Filter to find values in another range based on two cell values | Excel Programming |