Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Add value 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

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 exist value in another cell

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()
'Range("Sheet1A1").Value = Range("A1").Value + ActiveCell.Value 'only if the selected cell is on the same worksheet and adds to the already existing a value in the destination cell 'A1'
Worksheets("Sheet1").Range("A1") = Worksheets("Sheet1").Range("A1") + ActiveCell.Value 'added value from the selected cell from any worksheet, on the Sheet1 in the 'A1' cell on the existing a 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 another worksheet and chronological display of the copied values.


In this third example, I will show how we can use 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 this example Sheet1).

Figure 5.
How to quickly add the value of from selected cell on the another worksheet in the a specific cell

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-clicking on 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 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 Module in VBE for Excel