Reducing the need for Multiple hardline codes

[jcrain2v]

I have financial spreadsheet to keep track of the daily cost of a project from start to finish. Writing a code for say all the C3's was simply enough. How can I apply a macro to do this for the next 40 lines so I dont have to hardline it 44 times?

Sub updatethesum()
Dim ws As Worksheet
Dim i As Double
i = 0
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Total" Then
i = i + ws.Range("c3")
End If
Next
Sheets("Total").Range("c3") = i
End Sub

[mdmackillop]

Sub updatethesum()
    Dim ws As Worksheet
    Dim i As Double
    Dim j As Long
    
    For j = 3 To 43
    i = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Total" Then
            i = i + ws.Range("c" & j)
        End If
    Next
    Sheets("Total").Range("c" & j) = i
    Next j
End Sub

[jcrain2v]

Thanks mdmackillop. I'm new to VBA. I've used a little Fortran for my graduate in civil engineering and programmed a TI89 calc that is all. What would happened it I changed double to long for i?

[mdmackillop]

Long is an integer value so you would lose decimals, if any, from your data.

[jcrain2v]

Okay! This spreadsheet uses whole numbers, even though given how simple it is it really wouldn't make a difference. Can I repeat this code for two more columns in the totals sheet and add say "k" and "l", adjust and add if statements accordingly? ie. k=k+ws.Range ("e"&j)

next J
next k
next l

[mdmackillop]

An alternative to put formulae in the cells

Sub Test()
    Dim x As String
    x = Sheets(Sheets.Count).Name
    Sheets("Total").Range("C3:C42").FormulaR1C1 = "=SUM('Sheet2:" & x & "'!RC)"
End Sub

[SamT]

Sub Alternate_updatethesum() 
    Dim ws As Worksheet 
    Dim i As Long

With Sheets("Total")     
   For Each ws In ThisWorkbook.Worksheets 
      For i = 3 To 43 
            If ws.Name <> "Total" Then 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 'Edit to suit
                .Range("D" & i) =  .Range("D" & i) + ws.Range("D" & i) 
                .Range("E" & i) =  .Range("E" & i) + ws.Range("E" & i) 
                .Range("F" & i) =  .Range("F" & i) + ws.Range("c" & i) 
                .Range("G" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
                .Range("C" & i) =  .Range("C" & i) + ws.Range("c" & i) 
            End If 
        Next 
    Next
End With
End Sub

[jcrain2v]

Nice! Seeing Both of these is definitely helping me to understand how logic works with VBA. Sam T, you just gave me an idea on how to code the next thing I want to do.

[mdmackillop]

For multiple columns

Sub Test1()
    Dim x As String
    Dim i As Long
    x = Sheets(Sheets.Count).Name
    For i = 0 To 8   'change to suit
    Sheets("Total").Range("C3:C42").Offset(, i).FormulaR1C1 = "=SUM('Sheet2:" & x & "'!RC)"
    Next i
End Sub

[jcrain2v]

I'm gonna have to learn a little more about how some of that code works for your alternatives mdmackillop.

[mdmackillop]

Offset is simply Offset(row,column). Row can be omitted as in Offset(,column) and default 0 for row is implied.
The RC refers to cell position, absolute or relative.
RC in this case is the same cell in another sheet
R2C2 = cell B2
R[2]C = Offset(2,0)
R2C[5] = row 2 offset 5 columns
and so on.

Using the macro recorder will produce

Sub Macro1()
'
' Macro1 Macro
'


'
    Range("C3:C22").Select
    ActiveCell.FormulaR1C1 = "=SUM(Sheet2:Sheet4!RC)"
    Range("C3").Select
End Sub

with a little editing

Sub Macro1()
    Range("C3:C22").FormulaR1C1 = "=SUM(Sheet2:Sheet4!RC)"
End Sub

[jcrain2v]

Sam T i was able to get yours to work, but it looped. Mdmakillup I had trouble getting that last one you sent to work, but I a sure its due to my inexperience. This is what I ended up with.

Sub updatethesum()
    Dim ws As Worksheet
    Dim i As Double
    Dim j, k, l, m As Long
     
    For j = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("c" & j)
            End If
        Next
        Sheets("Total").Range("c" & j) = i
    Next j
    
    For k = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("e" & k)
            End If
        Next
        Sheets("Total").Range("e" & k) = i
    Next k
    
    For l = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("f" & l)
            End If
        Next
        Sheets("Total").Range("f" & l) = i
        
    Next l
    
    For m = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("g" & m)
            End If
        Next
        Sheets("Total").Range("g" & m) = i
        
    Next m
    
End Sub

It works exactly as needed. Is there a way to clean it up and still get the same function without looping. I am writing this for the office lady who does the data entry. I need to make sure it'll only calculate once in case the button gets hit more than once.

[jcrain2v]

I have been working on a financial spread sheet that totals the daily cost for various pieces of equipment on various projects. I with the input of a couple of people in the forum as able to come up with a macro the sums the daily input values for cells in a column. I am new to VBA and am requesting help for writing another macro that will update all current sheets at once. In our case we keep a daily operating cost of all our equipment. Our spreadsheet has a template sheet we copy and rename for each day. If we rent a piece of equipment or get a new machine and we enter it as a line item of the template sheet I would like that input to be transferred to all previous sheets and the totals sheet. Plus is there a way have my range 3 to 44 adjust automatically? If I add 5 pieces of equipment I'd have to adjust the macro to incorporate the 5 new line items. I don't have time to do this all the time as I performing civil engineering (drafting, design, estimating, and surveying) work for a contractor and the office person who uses the spreadsheet has no clue on how to do this stuff. Here is what I have thus far, if you can help me simplify it great!

Sub updatethesum()
    Dim ws As Worksheet
    Dim i As Double
    Dim j, k, l, m As Long
     
    For j = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("c" & j)
            End If
        Next
        Sheets("Total").Range("c" & j) = i
    Next j
    
    For k = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("e" & k)
            End If
        Next
        Sheets("Total").Range("e" & k) = i
    Next k
    
    For l = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("f" & l)
            End If
        Next
        Sheets("Total").Range("f" & l) = i
        
    Next l
    
    For m = 3 To 44
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
                i = i + ws.Range("g" & m)
            End If
        Next
        Sheets("Total").Range("g" & m) = i
        
    Next m
    
End Sub

Thanks in advance! Plus now when I think about it I should have known better, once we add it to the template sheet it'll be added every time for future sheets. Typed before I thought!

[SamT]

Sub SamT_updatethesum()
    Const TotalsRng As String = "C3:G44"

    Dim ws As Worksheet
    Dim Dest As Range
    Set Dest = Sheets("Total").Range(TotalsRng)
    
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Total" Then
             ws.Range(TotalsRng).Copy
             Dest.PasteSpecial Operation:=xlAdd
            End If
        Next
End Sub

Hmmmn? Column D?

[SamT]

I need to make sure it'll only calculate once in case the button gets hit more than once.

For how long?
This session of Excel?
Today?
Until Sheet is copied somewhere?
Forever?

What is the State of Sheet:=Total before this Summing?
Might need someplace on the sheet to store the info needed to prevent over stamping.

[jcrain2v]

I realize this can get super complicated, but right now I am just trying to establish something basic until I can learn more about VBA. I have a button to run the macro I wrote once the data entry has been done. My guess would be this session of excel. Once she copies the template sheet and changes its name she will simply go to the totals sheet and click the button. It seems a straight forward approach, and i fear that If I get it doing too much she may get freaked out.

[jcrain2v]

is there a way to not limit the cells to rows to 3 to 44. if we have to add additional line items is there a way to get that to change dynmanically. Sorry SamT i am completely new to VBA

[jcrain2v]

I need column D to remain static.

[SamT]

As you can/will see, I merged your other thread into this one. We like to keep all posts about the same project in one thread. It's 'cuz we gets confused eezy.

[SamT]

I realize this can get super complicated, but right now I am just trying to establish something basic until I can learn more about VBA.

performing civil engineering (drafting, design, estimating, and surveying) work

I am new to VBA and am requesting help for writing another macro that will update all current sheets at once. In our case we keep a daily operating cost of all our equipment. Our spreadsheet has a template sheet we copy and rename for each day. If we rent a piece of equipment or get a new machine and we enter it as a line item of the template sheet I would like that input to be transferred to all previous sheets and the totals sheet. Plus is there a way have my range 3 to 44 adjust automatically?

We can handle the D column by either saving it before Summing and replacing it after, or by two copy and paste operations on each ws.

Making the Copy Range dynamic is trivial.

As to adding new line items to all previous sheets, well, we here at VBAX haven't "surveyed" the existing work, yet, have we? Are you sure that the historical data sheets need to be updated with all (future to them) line items?

This is a slightly larger and more complex a project then you are able to see at the moment. Not like skyscraper or strip mall big, but like road side rest stop facilities complex. Can you upload a workbook with the template and Total sheet in it? I am assuming that all other sheets are filled out copies of the Template. BTW, is the template in this same Workbook?