Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2006

Tip: Looking for answers? Try searching our database.

Copy, paste as sum

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ouchini - 07 Mar 2006 10:24 GMT
Hello,

Is there an easy way to copy multiple values and paste the sum of these
values in one cell. So, I select the values of A.1, A.2 and A.3. I copy
the cells and wants to paste the sum in another worksheet.
davesexcel - 07 Mar 2006 11:06 GMT
=SUM(Sheet1!$A$1:$A$3)

I think this is what you mean!:)
Place this formula in other sheets, you will always get the sum of
sheet 1 A1:A3

Signature

davesexcel

Ouchini - 07 Mar 2006 15:36 GMT
Hello davesexcel,

I am affraid this is not what I mean. When I drag my mouse over several
cells with values and copy those cells, I want to paste the sum of them
in another cell. I sometimes have to select several cells which are not
in a range and want to avoid using a helpcell.

In the statusline I can see what the sum is so I want to paste the sum
in 1 cell and not the individual cells.

Ouchini
Miyahn - 08 Mar 2006 10:49 GMT
> In the statusline I can see what the sum is so I want to paste the sum
> in 1 cell and not the individual cells.

If you want to copy 'AutoCalculate' result according to your selection on
'AutoCalclate' menu, apply proper shortcut key to the following macro.

Option Explicit
Sub CopyAutoCalcResult()
 If TypeName(Selection) <> "Range" Then Exit Sub
 Dim ACF As String, Buf As String
 ACF = AutoCalcFunc
 On Error Resume Next
 With Application.WorksheetFunction
   Select Case ACF
   Case 2013: Buf = .Average(Selection)
   Case 2014: Buf = .CountA(Selection)
   Case 2015: Buf = .Count(Selection)
   Case 2016: Buf = .Max(Selection)
   Case 2017: Buf = .Min(Selection)
   Case 2018: Buf = .Sum(Selection)
   Case Else: Buf = ""
   End Select
 End With
 If Err Then Buf = ""
 On Error GoTo 0
 With CreateObject("htmlfile")
   .ParentWindow.ClipboardData.SetData "Text", CStr(Buf)
 End With
End Sub
'
Private Function AutoCalcFunc() As Long
 Dim aBtn As CommandBarButton
 With Application.CommandBars("AutoCalculate")
   For Each aBtn In .Controls
     If aBtn.State Then AutoCalcFunc = aBtn.ID: Exit For
   Next aBtn
 End With
 Set aBtn = Nothing
End Function

Signature

Miyahn (Masataka Miyashita) JPN
Microsoft MVP for Microsoft Office - Excel(Jan 2006 - Dec 2006)
HQF03250@nifty.ne.jp

Ouchini - 09 Mar 2006 14:32 GMT
Thank you for this great solution. Although I am not good in VBA, I
understand part of the code. Maybe Microsoft includes this function as
a right click dropdown menu item in a future version of Excel.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.