Calc Code between Sheets...

[m0atz]

Afternoon all,

Just a quick point in the right direction if you dont mind...

I have two workbooks Book1 and Book2. Book1 is a list of shop names in Col A and total sales in Col B. Book2 is the weekly update I get with the figures, at present what i am doing is manually going through the list of stores in Book2 on each line and adding the total to the list in Book1.

What I'm after is some brief code to be used as part of a wider VBA tool to lookup the store name in Book2, locate it in Book1 and add the value already in Book1 to Book2 thereby giving me a running total of sales.

I'm sure this is pretty simple code, but i just need a quick start in the right direction.

much appreciated

colin

 

[kgkev]

You could vlookup the shop name into a helper column, then add the two values into a second column, then move these back to first column as a value only.

Excel Workbook
	A	B	C	D
1	shop 1	11000	1000	12000
2	shop 2	22000	2000	24000
3	shop 3	33000	3000	36000
Main

Sheet("MAIN").Range("D1:D1000").Copy
    Range("B1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

end sub

 

[m0atz]

That's a great idea thank you, I'll get cracking on putting that into practice as I'm sure it will be a time saver.

I have another question, I deal with phone bills for my company. The bills come in for about 5 of the staff, some of them in different formats. I have to produce reporting etc on their usage and costs etc, so I compile the billing into one workbook, with a master tab and then five other sheets for each employee bill. Sometimes the numbers show up without the zero at the start ie 7555123456 and sometimes its 07555123456. What i am looking for are the common numbers that they are all ringing ( we get a deal with the provider for the most commonly called numbers etc and they need this manually updating).

What i'd like is another sheet which shows these common numbers, however they may not be common to all employees, i.e. it might only be employee 1, 2 and 3 ringing a number and 4 and 5 might be ringing the same etc. Is there a way to show the numbers that appear in multiple across the worksheets in the workbook, bearing in mind the format of the number would have to be same i'm assuming?

Hope that makes sense...!!

 

[shyrath]

Try something like this for the totals thiswk and total
Change lastrow1 /2 and rng1 /2 to suit

Excel Workbook
	A	B	C	D	E
1	Sheet1	Total		Sheet2	Thiswk
2	Store	Sales		Store	Sales
3	A	10		A	1
4	B	88		B	2
5	C	64		C	3
6	A	76		A	4
Sheet2

Sub storetotals()

'this week
lastrow1 = Worksheets("Sheet2").Range("D65536").End(xlUp).Row
rng1 = "D3:D" & lastrow1

'total
lastrow2 = Worksheets("Sheet2").Range("A65536").End(xlUp).Row
rng2 = "A3:A" & lastrow2

    For Each c In Range(rng1)
    
    strf = c.Value
    salesthiswk = Cells(c.Row, 5).Value
    
    Range(rng2).Select
    
        Selection.Find(What:=strf, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value + salesthiswk

    Next c
    
End Sub

 

[kgkev]

That's a great idea thank you, I'll get cracking on putting that into practice as I'm sure it will be a time saver.

I have another question, I deal with phone bills for my company. The bills come in for about 5 of the staff, some of them in different formats. I have to produce reporting etc on their usage and costs etc, so I compile the billing into one workbook, with a master tab and then five other sheets for each employee bill. Sometimes the numbers show up without the zero at the start ie 7555123456 and sometimes its 07555123456. What i am looking for are the common numbers that they are all ringing ( we get a deal with the provider for the most commonly called numbers etc and they need this manually updating).

What i'd like is another sheet which shows these common numbers, however they may not be common to all employees, i.e. it might only be employee 1, 2 and 3 ringing a number and 4 and 5 might be ringing the same etc. Is there a way to show the numbers that appear in multiple across the worksheets in the workbook, bearing in mind the format of the number would have to be same i'm assuming?

Hope that makes sense...!!

No problem
about the second problem - I'd post this as a new topic if I were you.