Excel tutorials

Add Value from Active Cell to Ceratin Cell

Adding value from active cell on the exist a value to a specific cell

If you have a need to be added or add value from the selected or active cell from an Excel worksheet to a specific worksheet in a specific cell then read this tutorial and see how it can be using VBA macros or formulas, add a value from active cell into a specific cell on the specified Excel worksheet.

Example 1.

For the first example, the addition of certain cells that are on the same worksheet or on another worksheet.

We have a situation like in the picture below

Figure 1.

How to sum values ​​from specific cells ​​to certain cells from list 

In the picture above you notice that our target that in a cell 'F2', we have the result of the sum of all cells that are found in cells in the list.

In column 'A' enter addresses cells in which he finds value you want to add in a cell 'F2'

In column 'B' set of formulas that will copy value from cells in the same row in column 'A'.

This formula for Excel 97-2003 as follows:

=IF(ISERROR(INDIRECT(A2));"";INDIRECT(A2)) copy it to down.

This formula for Excel 2007 and above is as follows:

=IFERROR(INDIRECT(A2);"") copy it down.

In cell 'F1' we enter a range of data from column 'B' with the values ​​that we want to add. In this cell it is desirable to define the name of a range of data from column 'B' to a simple formula, then formula is as follows: =SUM(INDIRECT(data)) or in the second case we can write the formula as =SUM(INDIRECT(F1))

In the cells 'C13', 'D16', 'E11' you notice values ​​that were copied in column 'B' and are automatically added to the cell 'F2'.

Example 2.

Automatic summing a value, selected or active cell with already existing a value in a cell on the another worksheet

In this second example I want to show you how to use VBA macros can in one step to add value, which is located in the selected or active cell with any worksheet to a specific worksheet in a specific cell.

In the picture below you notice the cell 'A1' in which we want to add value to other worksheets from selected or active Excel cell.

Figure 2.

How to add value from the selected or active cell into a specific cell of another Worksheet by VBA 

In the picture below you notice the cell and value in them, that we want to add to the existing a value in cell 'A1' on the Sheet1 (image above - Figure 2). In the example below I have selected a cell 'A1' on the Sheet3, I want this value in one step added to on the existing a value in cell 'A1' on the Sheet1. This summing of the selected cell to another cell repeat for each selected or active cell.

Figure 3.

How to automatically add value from another worksheet from selected cells 

Automatic add value to selected value in another cell by VBA macro

This addition of the selected cell/a value in a specific cell on the Sheet1 we can do using VBA macro.

VBA macro below, copy into Module1 your Workbook

Sub AddSelectedValueToAnotherExistValue()
'only if the selected cell is on the same worksheet and adds to the already existing a value in the destination A1 cell
'Range("Sheet1").Value = Range("A1").Value + ActiveCell.Value
'added value from the selected cell from any worksheet, on the Sheet1 in the A1 cell on the existing a value
Worksheets("Sheet1").Range("A1") = Worksheets("Sheet1").Range("A1") + ActiveCell.Value
End Sub

I must mention that after running VBA macros can not go back, so you can not use the UNDO command, which can be inconvenient if you make a mistake a value. To avoid this problem, see Example 3.

So the process is as follows:

  1. Select the cell on the a worksheet that contains the value and you want to automatically add (SUM) on the Sheet1 from the value of 'A1' cell
  2. Press Alt+F8 (in the Run dialog window selected your VBA macro)
  3. Click the 'Run' button
  4. Now the a value from selected cells add together from values ​​between the cell 'A1' on the Sheet1
  5. and so on with each sheet

IMPORTANT: After starting the VBA macros , you can not use UNDO button (so you can not go back a step backwards)

Example 3.

Adding the value of the active cell with a value in a cell on the another worksheet and chronological display of the copied values.

In this third Excel example I will show how we can using VBA with extra column in chronological order to add value from selected or active cell from a Worksheet on the 'Sheet1' and the final sum in which you can have in one specific cell. In case you made ​​a mistake and entered the wrong a value, you can delete the last entered a value and delete the date and time of entry.

In the picture below you notice how it looks in the table by copying the values ​​of selected cells from other worksheets within the workbook. After copying the values ​​from the selected cell, Excel using VBA respective this a value is copied into the first blank cell in column 'A'. In column 'B' automatically displays the date and time on which that a value copied. The date and time to help us know when we copy a specific value, but if we made a mistake we can delete the last row. In cell A1 there is a formula that adds the copied values. =SUM(A2:A40)

Figure 4.

How to copy a value from the active cell on the another worksheet and a display of the chronological time copying 

Thus, by using VBA macros we can copy it to another worksheet a value in the active cell, we can also have a list of copied values ​​with chronological order.

In the picture below, you notice 'Sheet2' on it a value in the selected/active or highlighted cell. By running a VBA macro Excel automatically copies the value of the highlighted cell to another worksheet in the be the next empty row/cell (in the this Excel example 'Sheet1').

Figure 5.

How to quickly add the value of from selected cell on the another worksheet in the a specific cell via VBA 

VBA macro for copying the active cell into the first empty row on the another worksheet

This VBA macro copy in Module1 your Workbook, so that you can use and run from any Excel worksheet

Sub CopyInTheFirstBlankRow()
'Copy the value marked cells on the Sheet1 in the first blank cell next
ActiveCell.Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

VBA macro for date and time display, at the moment when we perform copying some value in the highlighted cell.

This VBA macro copy in the Sheet1-module in VBE (see Figure 5.), by right-click on the the name of the tab/worksheet Sheet1 => View Code

Private Sub Worksheet_Change(ByVal Target As Range)
'VBA code for automatically date, which shall be permanently remembered after entering in the column A
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1) = Date & " " & Time 'the number 1 indicates the offset one column from column A, column B is the result and it will display the date and time
End Sub

Types of Modules in Excel VBE

Figure 5.

How to insert types of Module in VBE for Excel 

Add value to an existing value in the adjacent cell

The subject of this topic it could also be the Automated SUM of the entered values to an existing value in the adjacent cell

In this example, note that the entered value is automatically added to the existing value in the right adjacent cell. After entering the number or value of the "D" column, the value is automatically added to the existing value of the neighboring cell 'E' column. If you have a need for such a sum, then use this VBA macro below the image.

Sum the entered value to the existing value

'Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim int_range As Range
Set int_range = Intersect(Target, Range("D2:D10"))
If Not (int_range Is Nothing) Then
For Each c In int_range
c.Offset(0, 1).Value = c.Offset(0, 1).Value + c.Value
Next
End If
End Sub

Copy this VBA macro to the Sheet module in VBE by right-clicking on the worksheet tab name  => View Code