Summing 2 columns & storing result in different sheet 1

[Stretchwickster]

Hey people!

I have a source sheet from which I need to sum all of the values in 2 columns and store the result in a target sheet elsewhere. I don't want to have any links with the source sheet after computing the sum, I simply want to pluck the values from each column, sum them, and store just the values in the target sheet.

The only way I can think of is to copy the two columns to a hidden area, then sum them, then copy the sums to the target sheet's column. I can do this but I wondered if there was a better method or if Excel has a built-in function to do this.

Your help would be much appreciated!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

1: Do you want 2 values storing or just 1 for the 2 columns??
2: Do the totals get overwritten each time or do you want a list of totals ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[Stretchwickster]

1. I want 1 column of results stored using the 2 source columns
2. The total get overwritten each time

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Zathras]

Clive, here is one way to do what I think you are trying to do:
[blue]

Option Explicit

Sub test()
  SumColumn "1.xls", "Sheet1", "A", "B2"
  SumColumn "1.xls", "Sheet1", "B", "B3"
End Sub

Sub SumColumn(BookName As String, SheetName As String, _
       ColumnID As String, Destination As String)
Dim wkb As Workbook
Dim wks As Worksheet
Dim nSum
Dim bOk As Boolean
  On Error Resume Next
  Set wkb = Workbooks(BookName)
  If wkb Is Nothing Then
    MsgBox "Workbook " & BookName & " is not open."
  Else
    With wkb
      Set wks = .Worksheets(SheetName)
      If wks Is Nothing Then
        MsgBox "Worksheet " & SheetName & " not found in " & BookName
      Else
        With wks
          nSum = WorksheetFunction.Sum(.Range(ColumnID & ":" & ColumnID))
          bOk = True
        End With
      End If
    End With
  End If
  If bOk Then
    Range(Destination).Value = nSum
  Else
    Range(Destination).Formula = "=NA()"
  End If
End Sub

[/color]
If the workbook not open situation is common, you could replace the error message with logic to open and then close the source workbook after taking the sum.

You may also want to add code to be sure the correct book is active when running the macro or the sums will go into the wrong book.

 

[Zathras]

Sorry, I thought you were looking for two sums. If you want a column where each cell is the sum of two cells from the corresponding row, then try this variation:
[blue]

Option Explicit

Sub test()
  SumColumn "1.xls", "Sheet1", "A:B", "B"
End Sub

Sub SumColumn(BookName As String, SheetName As String, _
       Columns As String, Destination As String)
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim i As Long
  On Error Resume Next
  Set wkb = Workbooks(BookName)
  If wkb Is Nothing Then
    MsgBox "Workbook " & BookName & " is not open."
  Else
    With wkb
      Set wks = .Worksheets(SheetName)
      If wks Is Nothing Then
        MsgBox "Worksheet " & SheetName & " not found in " & BookName
      Else
        With wks
          Set rng = Intersect(.Range(Columns), .UsedRange)
          For i = rng.Row To rng.Rows.Count
            Cells(i, Destination) = _
              WorksheetFunction.Sum(Intersect(.Range(Columns), _
                                       .Range(i & ":" & i)))
          Next i
        End With
      End If
    End With
  End If
End Sub

[/color]
Of course, if the source columns are not contiguous, then modification to the sum formula is necessary.

 

[Stretchwickster]

Cheers Zathras - your second solution worked a treat!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)