Macro or Formula help needed

[SARABECK]

Hello,

I need help with a formula or a macro that will help me get total dollars for contract that close for the month.

I have 5 workbooks and each of them have 40 to 50 tabs (depending on OPEN/CLOSE contracts for the month). My tabs are named "Vendor-name, Contract##, Open" (which indicated the contract is open for the month). For contract that got closed for the month are labeled "Vendor-name, Contract###) basically the same as open contract without the word open in the title.

Row 11 Column B provides the commodity code for the contract in that tab - I need help with a formula (if possible) or Macro that sums up row70 column R across all tabs for a specific commodity that does not have the word "open" in the title.

for example, Workbook#1 I have the following tabs:
Contract #1 OPEN - B11 "ZZ"
Contract #2 - B11 "ZZ"
Contract #3 OPEN - "ZZ"
Contract #4 - B11 "YY"
Contract #5 - B11 "ZZ"
Contract #6 - B11 "YY"

I want formula or macro that would provide a total for commodity "YY" and "ZZ" for contract that closed. At the moment, i have a formula that added up all dollars in R70 across all of the tabs whether the commodity is close or open.

Any help would be appreciated.

Thanks.

 

[riddenstudios]

Do you still need help with this? If so do you have excel 2007 or newer? I will come up with something very soon if you do.

 

[SARABECK]

Yes, I still need help with this. I have Excel 2010. I would like it if the dollars in R70 are listed out by contract (name in the tab) if that's not possible than sum would work.

What i do is match dollars that came through the ledger for contract that close.

thanks in advance.

 

[SARABECK]

Can someone please help me with this? Thank you.

 

[riddenstudios]

Sorry it took so Long. Had a few issues. This is the only way I could get it to work. I used a button on the first worksheet. You can put a button on a "totals" worksheet or where ever you are going to store this information. If you know how to use the add-in feature for the ribbon up top this would be a great place. I forget at the moment but I know there's info out there or write back. I explained the code and then what I had to do after it. Please read for a better understanding.


Here is the code:



Option Compare Text

Private Sub CommandButton1_Click()
Dim mysum As Long
Dim sh As Worksheet
mysum = 0
For Each sh In ActiveWorkbook.Sheets
If InStr(sh.Name, "closed") Then mysum = mysum + sh.Cells(18, 70)
Next sh
MsgBox mysum
End Sub

1. I could only get it to read all the tabs (worksheets) with a common name. I chose "closed" to the tab name I wanted to sum. You can do this manually or by:

activesheet.name = activesheet.name & " closed"

<tbody>
</tbody>

2. sh.cells(18,70) IS cell R70 from what I gathered this is the common cells you will need added? You can change this but it's important to have a common cell in each worksheet. Notice it goes by column number then row number.

3. The last line MsgBox mysum currently will message box the added answers. You can change this easily putting it in a worksheet and cell range. For instance a new worksheet called "totals" in cell A1 should be: Worksheets("totals").Range("A1")

I hope this helps?!

Andrew

 

[SARABECK]

Hi Andrew,

No problem, thank you so much for replying back and i really appreciate your help (you have no idea how much this will help me).

I tried to add your macro to my file for some odd reason its not working for me. The message i get is "0". I changed "close" to "OPEN".

The link below for Book1 is a replica of the file i'm working with.

Book1.xlsm

please let me know if you like me to clarify anything.

thank you again.

 

[riddenstudios]

Found the problem it was me:

In this line switch the cell numbers around should = .... sh.cells(70,18)

Hope this time around it works better!

Hey just a reminder, make sure your code is with the button, double click the button to make sure, if its in the wrong worksheet it won't work correctly.

My guess is you know what your doing in general, you put the code in the module just for me but wanted to double check.

Please let me know in either case.

Good Luck! No problem.

 

[MGORIYA]

Hi...


Try this out...
Note : I considere that in your workbook, tab (sheet) name contains "Open" for Open contract and without word "Open" or "Closed" as closed contract.
Sub Closed_Contract_Sum()

Dim Total_Done As Long
Dim ws As Worksheet

Total_Done = 0

For Each ws In worksheets

If InStr(ws.Name, "Open") Then
'do nothing
Else
Total_Done = Total_Done + ws.Cells(70, 18)
End If

Next

MsgBox "Total Closed Contract Value is : " & Total_Done

End Sub



Regards,

 

[SARABECK]

Found the problem it was me:

In this line switch the cell numbers around should = .... sh.cells(70,18)

Hope this time around it works better!

Hey just a reminder, make sure your code is with the button, double click the button to make sure, if its in the wrong worksheet it won't work correctly.

My guess is you know what your doing in general, you put the code in the module just for me but wanted to double check.

Please let me know in either case.

Good Luck! No problem.

thank you so much Andrew!!! - the code works GREAT in my example sheet that I provided, but for some reason not in my original workbook - maybe because there are few analysis tabs before the contract tabs starts and the error i get is "Run-Time error 13" "Type mismatch" and when i debug - it highlights this line
mysum = mysum + sh.Cells(18, 70).

I don't know whats wrong. I even added a sheet call "Total" right before my contracts tab starts and it still didn't work. But thank you so much for helping me out.

 

[SARABECK]

Hi...


Try this out...
Note : I considere that in your workbook, tab (sheet) name contains "Open" for Open contract and without word "Open" or "Closed" as closed contract.
Sub Closed_Contract_Sum()

Dim Total_Done As Long
Dim ws As Worksheet

Total_Done = 0

For Each ws In worksheets

If InStr(ws.Name, "Open") Then
'do nothing
Else
Total_Done = Total_Done + ws.Cells(70, 18)
End If

Next

MsgBox "Total Closed Contract Value is : " & Total_Done

End Sub



Regards,

Thank you MGORIYA, your code works too but it provides the total for both open and close, it should provide total for open contracts tabs only. I also have the same problem, it works in the example file i provided but in my original file. but thank you for help me out.