Calc Code between Sheets...

m0atz

Board Regular
Joined
Jul 17, 2008
Messages
247
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,247
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
ABCD
1shop 111000100012000
2shop 222000200024000
3shop 333000300036000
Main




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

m0atz

Board Regular
Joined
Jul 17, 2008
Messages
247
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

Active Member
Joined
Jun 28, 2005
Messages
483
Try something like this for the totals thiswk and total
Change lastrow1 /2 and rng1 /2 to suit

Excel Workbook
ABCDE
1Sheet1TotalSheet2Thiswk
2StoreSalesStoreSales
3A10A1
4B88B2
5C64C3
6A76A4
Sheet2




Code:
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

Well-known Member
Joined
Jun 24, 2008
Messages
1,247
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,484
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top