pasting a hi-lighted auto sum

[Guest]

In excel, when you hi-light a column of numbers, the program automatically
sums the hi-lighted numbers. My question is this, is there a way to copy
this auto sum total to another speadsheet that I have open?

Thanks for your help!

 

[Gord Dibben]

Mark

I assume you mean the SUM down on the Status Bar.

AFAIK there is no way to copy this number.

Workaround........select the cells then hit the AutoSum button on your
Standard Toolbar.

Copy and Paste Special>Values to the other workbook or sheet.


Gord Dibben Excel MVP

 

[Guest]

Thanks for your help Gord. I have tried the method that you suggested,
however when you do select the cells and hit the autosum button, it will
insert the figure into the column. You then have to copy the newly inserted
cell and continue with the copy and paste as you suggested.

When I am working with these columns of numbers I typically will not be able
to have the new cell with the autosum figure inserted at the bottom of the
current row. I would also then have to delete the cell.

I was hoping there was another way.

Thanks again for your help.

 

[Gord Dibben]

Mark

How about a macro to sum the cells and place the value on another sheet?

Sub Sum_Selected()
Dim Rng As Range
Set Rng = Selection
If Rng.Cells.Count < 2 Then GoTo endit
Selection.Cells(1).Name = "oldrange"
Sheets("Sheet2").Range("A1").Formula = "=Sum(" _
& ActiveSheet.Name & "!" & Rng.Address & ")"
Sheets("Sheet2").Select
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="oldrange"
Exit Sub
endit:
MsgBox "You have not selected a range"
End Sub


Gord Dibben Excel MVP

 

[Guest]

Mark

How about a macro to sum the cells and place the value on another sheet?

Sub Sum_Selected()
Dim Rng As Range
Set Rng = Selection
If Rng.Cells.Count < 2 Then GoTo endit
Selection.Cells(1).Name = "oldrange"
Sheets("Sheet2").Range("A1").Formula = "=Sum(" _
& ActiveSheet.Name & "!" & Rng.Address & ")"
Sheets("Sheet2").Select
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="oldrange"
Exit Sub
endit:
MsgBox "You have not selected a range"
End Sub


Gord Dibben Excel MVP

 

[Guest]

I will give that a try, I'm not too verse with macros, but I will give it a
shot.

Thank you very much!

 

[Gord Dibben]

Mark

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Select a range of cells with numbers to sum up on the active sheet.

If you have a Sheet2(or edit Sheet2 in the code to your sheetname) the value
will be pasted there at A1.

Tools>Macro>Macros. Select the macro "Sum_Selected" and run it.

The macro can be assigned to a button or shortcut-key combo.


Gord