Excel place totals

  Terry Brown 11:20 27 Feb 2008

What I would like to do create an account (finance) and with the total move (or copy) it to another page automaticly. The problem being I want the moved (copied) total put in a different place.

e.g. if the total (page 1) is in a50, I want it copied (moved) to page 2 a10, and on the next listing moved to the next available empty box, so the original sum is left intact
This would be in the format *(e.g) box a9 would be the date transferred and a10 would be the data.Te next time it was run the date would be in b9 and the data in b10.
I have had a look at IF but it does not seem suitable.

  VoG II 11:34 27 Feb 2008

I think that you will need a macro for this since a formula can only 'pull' data, it can't 'push'.

Will the total always be in A50?

Eventually you will run out of columns on the sheet that the data is copied to. What should happen then?

  Terry Brown 16:23 27 Feb 2008

I am trying to set up an expenses sheet where each week the totals are 'saved' on a separate line for checking, so i can look at (e.g) period 10 Jan 2008 and compare it with (e.g.) 4 Feb 2008.
Does this make it any clearer ?
Thanks for replying

  VoG II 17:01 27 Feb 2008

This macro will read the last value in column A of Sheet1 and write it to the next available column in row 10 of Sheet2 with the date above it. Once available columns are about to run out it will prompt to clear rows 9 and 10 on Sheet2 and start again from column 1.

Sub CopyTotal()
Dim NextCol As Integer, LastRow As Long, Response As Integer
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Sheet2")
If IsEmpty(.Cells(9, 1).Value) Then
NextCol = 1
NextCol = .Cells(9, Columns.Count).End(xlToLeft).Column + 1
If NextCol > 255 Then
Response = MsgBox(prompt:="Column limit reached: Clear contents?", Buttons:=vbYesNo + vbExclamation)
If Response = vbNo Then Exit Sub
NextCol = 1
End If
End If
.Cells(9, NextCol).Value = Date
.Cells(10, NextCol).Value = Sheets("Sheet1").Range("A" & LastRow).Value
End With
End Sub

Press ALT + F11 to open the Visual Basic Editor, Insert Module. Copy and paste the macro into the white window then close the VBE. To run the macro Tools > Macro > Macros, highlight CopyTotal and click the Run button.

If you add a button to the sheet from the Forms toolbar you can assign the macro to it to make things simpler.

  Terry Brown 18:40 03 Mar 2008

Thanks VOG, I was hoping you would come through (again), please do not leave this help page, as you are an invaluable help with Excel.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?