VBA for sum the same cell

[tazz]

Hello all,
I would like to sum all A1 cells from a workbook in C1 in "Main"spreadsheet of the same workbook. Also data from "Main" should not be counted.
The number of sheets it is unknown
Thank you for your help.

 

[SirJB7]

Hi, tazz!
Just to put things clear:
- you have 2 workbooks: actual and that referenced in cell A1 of worksheet "Main"
- you don't know how many worksheets the referenced workbooks has
- do you know the names of the 1st and last ones?
- you want to sum all A1 cells in all but "Main" worksheets of the referenced workbook
- you want to place the sum anywhere in actual workbook
- are you looking for a formula only solution or VBA is allowed?
Regards!

 

[tazz]

Hi, SirJB
It is only 1 workbook and Main is the spreadsheet where I would like to have this sum.
I want to exclude Main because I also have some data which I would like not to be counted. As far as VBA, my guess is it will take less computer resources.
Thank you.

 

[SirJB7]

Hi, tazz!

If you know the 1st and last worksheet names you'd do something like this, in cell C1 of worksheet Main, for example:
C1: =SUM('<1st ws name>:<last ws name>'!A1)-Main!A1

Does this help? If not, place the following code in any standard module and call it as:
C1: =AlmostSumAll()

Option Explicit
 
Function AlmostSumAll() As Long
    ' constants
    ' declarations
    Dim lSum As Long, I As Integer
    ' start
    lSum = 0
    ' process
    For I = 1 To Worksheets.Count
        With Worksheets(I)
            If .Name <> ActiveWorkbook.Name Then lSum = lSum + .Cells(1, 1).Value
        End With
    Next I
    ' end
    AlmostSumAll = lSum
End Function

Just advise if any issue.

Regards!

 

[tazz]

Hi SirJB,

Fore some reasons I can't make this one working.
This is what I did:
1. I inserted a module and renamed it as AlmostSumAll
2.I changed the"Name" in line 11 with my workbook's name(in both places)

Thank You
PS:I don't now what to do with C1: =AlmostSumAll()

 

[SirJB7]

Hi, tazz!
1) Ok, but no need to assign any particular name to the module, it can remain Module1 or so.
2) In the 11th line it should say Activesheet instead of Activeworkbook, so as to avoid summing the A1 cell of worksheet Main (assuming that you were going to place that formula in any cell of that worksheet, C1 in the example)
PS: In worksheet Main, cell C1, type the formula:
=AlmostSumAll()
Hope it helps.
Regards!

 

[tazz]

Hi SirJB,
I tried to implement the changes you told me about. I still can't make this one working.
If your schedule allows you, please take a look at the attached file and let me know what I did wrong.
Thank you.

 

[NARAYANK991]

Hi ,

Try this :

Function AlmostSumAll() As Double
    Application.Volatile
    Dim lSum As Double
    Dim I As Integer
 
    lSum = 0
 
    For I = 1 To Worksheets.Count
        With Worksheets(I)
            If .Name <> "Main" Then lSum = lSum + .Cells(1, 1).Value
        End With
    Next I
    AlmostSumAll = lSum
End Function

Narayan

 

[tazz]

Hello,
I tried your code and is still not working. Perhaps I keep making the same mistake all the time. Could you please change and upload the file I attached to try to understand where my mistake is?
One more question: first line As Double means it will apply only at 2 sheets?
Thank you for your time

 

[tazz]

Hello,
I copied the formula from C1 in A1 and I saw that is working. Then I put back the formula in C1 and is still working. I don't know why it didn't work from the first time but most important thing is that this code is working(and I checked with multiple pages not only 2 )
Thank you so much for your help.

 

[SirJB7]

Hi, tazz!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!