VBA code problem

[Yaron Yehezkel]

Hey guys,

I have some problem with my code and i hope to fine here the solution.

I have a data of employees and their monthly salary. At the end of each table, in each of the 5 specific sheets, is the "Monthly Salary in NIS" column.
I need that the total amount of all employees monthly salary (the sum) will be placed under the column "Monthly Salary in NIS" and one cell after the last data line.

The problem is that in each sheet, where the "Monthly..." columns is, there are different number of rows and columns (because not all the employees worked all those 5 months). so the thing is that i need that mt code will sum dynamically those columns (if i will add more rows, the calculation still will be placed one cell after the last data line in the specific columns) - at my code i set a particular cell, which is not my intention but i couldn't find the solution:

Sub SumIf()

    ActiveCell.FormulaR1C1 = "=SUM([Monthly Salary in NIS])"
       Range("F18").Select
       Sheets("employees 1").Select
      
    ActiveCell.FormulaR1C1 = "=SUM([Monthly Salary in NIS])"
       Range("G20").Select
       Sheets("employees 2").Select
      
    ActiveCell.FormulaR1C1 = "=SUM([Monthly Salary in NIS])"
       Range("H9").Select
       Sheets("employees 3").Select
      
    ActiveCell.FormulaR1C1 = "=SUM([Monthly Salary in NIS])"
       Range("I40").Select
        Sheets("employees 4").Select
       
    ActiveCell.FormulaR1C1 = "=SUM([Monthly Salary in NIS])"
       Range("J202").Select
       Sheets("employees 5").Select

End Sub

Thank you!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of one or two of your sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Yaron Yehezkel]

Ok,
So, I have a workbook full of sheets. five of them are called: Employees (1-5).
In each sheet, there is a table of data. In the end of the table there is a column called "Monthly salary in NIS".
The tables aren't equally with the columns and the rows.

I need to write a code that calculate the last column in those five sheets, the "Monthly salary in NIS" and present the calculation one cell after the last cell of data in that last column.

The problem is that i need it to be dynamically. It's means that if i'll add some more rows in the future, the calculation will still be presented one cell after the last cell of data in that column (the code that i sent in that post is referring to specific and permanent cell (for example - "Range("J202").Select").

* I've marked the sum spot in yellow. which need to be dynamically (but always in the last column!)

attached XL2BB of two of my sheets for the visual explanation.

Appreciate!

Excel Employees.xlsb
	A	B	C	D	E	F
1	Employee ID	First Name	Last Name	Branch	Department	Monthly Salary in NIS
2	10001				Accounting	6210
3	10002				Computing	4095
4	10003				Planning	6066
5	10004				Warehouse	12060
6	10005				Accounting	7636
7	10006				Sales	6246
8	10007				Transport	8851
9	10008				Development	7704
10	10009				Sales	6255
11	10010				Warehouse	10350
12	10011				Sales	7326
13	10012				Warehouse	6817
14	10013				Planning	4365
15	10014				Human Resources	10071
16	10015				Development	7470
17
Employees1

Excel Employees.xlsb
	A	B	C	D	E	F	G
1	Employee ID	First Name	Last Name	Branch	Department	Position	Monthly Salary in NIS
2	10001				Accounting	Assistant Director	6210
3	10002				Computing	Supporter	4095
4	10003				Planning	Assistant Director	6066
5	10004				Warehouse	Assistant	12060
6	10005				Accounting	Head of	7636
7	10006				Sales	Assistant	6246
8	10007				Transport	Head of	8851
9	10008				Development	Assistant	7704
10	10009				Sales	Department Director	6255
11	10010				Warehouse	Team Leader	10350
12	10011				Sales	Employee	7326
13	10012				Warehouse	Employee	6817
14	10013				Planning	Department Director	4365
15	10014				Human Resources	Team Leader	10071
16	10015				Development	Head of	7470
17	10016				Computing	Assistant Director	11448
18	10017				Planning	Team Leader	8505
19	10018				Accounting	Branch Director	12672
20
Employees2

 

[mumps]

Try:

Sub GetSum()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lCol As Long, ws As Worksheet, ColLetter As String
    For Each ws In Sheets(Array("Employees1", "Employees2", "Employees3", "Employees4", "Employees5"))
        With ws
            lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            ColLetter = Replace(.Cells(1, lCol).Address(False, False), "1", "")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            .Cells(LastRow, lCol).Formula = "=sum(" & ColLetter & "2:" & ColLetter & LastRow - 1 & ")"
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Why not just add a total row to your table?
On the table design tab select Total row & then you can add the sum in there.

 

[Yaron Yehezkel]

Try:

Sub GetSum()
    Application.ScreenUpdating = False
    Dim LastRow As Long, lCol As Long, ws As Worksheet, ColLetter As String
    For Each ws In Sheets(Array("Employees1", "Employees2", "Employees3", "Employees4", "Employees5"))
        With ws
            lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            ColLetter = Replace(.Cells(1, lCol).Address(False, False), "1", "")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            .Cells(LastRow, lCol).Formula = "=sum(" & ColLetter & "2:" & ColLetter & LastRow - 1 & ")"
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

That code is good for me but the thing is that it's continuing calculating the column each time i press the wired button.
Instead of writing a code, i tried to record Fluff idea and it actually worked perfect!

Thanks for your time and effort! Appreciate!

 

[Fluff]

Glad we could help & thanks for the feedback.