Excel place totals

  Terry Brown 11:20 27 Feb 08
Locked

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.
Terry

  VoG II 11:34 27 Feb 08

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 08

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
Terry

  VoG II 17:01 27 Feb 08

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
Else
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
.Range("A9:IV10").ClearContents
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 08

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.
Terry

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

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…