auto fill data

[Guest]

Following is the information available:
Data is manually entered into Columns A, B as it comes from three different
sources. C gives total of A and B, whereas D totals Column C for a day.
On second day, fresh data is entered from Row 4 downward. Column D
starts totalling Column C for next day only.

Sheet1

A B C D
1 20 25 45 45
2 18 14 32 77
3 2 22 24 103 DAY 1
4 25 10 35 35
5 10 15 25 60 and so on.

What I am looking for is to have a grand total in another worksheet (may or
may not be in same file), where only Total figure at the end of day shall be
entered.
Each days final total shall be entered one below other automatically and
shall be totalled. The input shall have to be last filled value from sheet 1
column D, whenever date changes.
Kindly help

 

[Guest]

Sub Movetotals()

Const Path = "c:\temp\"
Const totalfile = "book1.xls"
Const totalsheet = "sheet1"

LastRowData = Cells(Rows.Count, "A").End(xlUp).Row
'Last days work is the last 4 rows
'Need to subtract total of Col C from Col D
Set Colc_Data = Range("C" & (LastRowData - 3) & _
":C" & LastRowData)
Set ColD_Data = Range("D" & (LastRowData - 3) & _
"" & LastRowData)

'Get totals from column c and d
TotalD = WorksheetFunction.Sum(ColD_Data)
Grandtotal = TotalD - _
WorksheetFunction.Sum(Colc_Data)

'Open total Workbook
Workbooks.Open Filename:=Path & totalfile
With Worksheets(totalsheet)
LastRowTotal = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & (LastRowTotal + 1)) = Grandtotal
End With

ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[Guest]

Thanks, but the macro, when run returns "400".

One more information, the grand total table should always be looking for
latest
value in column "D", and as soon as date changes, it should pick up last
days final total. There will be a day column, column "A". All columns shift
to right by one. Also I wrote three inputs per day, but they may vary.
Mistake is regretted.
Pl help.

 

[Guest]

I don't know how to tell how many rows to process each day. What data tells
me when each days data starts. Is there a date or some other information to
use.

Error 400 iis usually caused by VBA not being able to access a file. Usally
its when a file is on a network. Sometimes caused by access priviliges. I
tried repeating the error 400 by changing the filename, path, and worksheet
name and none of these changes gave me the error 400.

The code assumes the data is in the active workbook and worksheet. Let me
klnow if you find the problem and which statement is causing the problem.
try closing all workbooks except the one workbook which contains both the
data and macro.

 

[Guest]

I think, I have simplified my requirement, and is given below:

worksheet1

A B C
1 29-10-07 45
2 55
3 25
4 36 161
5 30-10-07 50
6 40 90
7 31-10-07 25

This is basic structure.
Inputs are entered in Column B, one after other
as they come ( they may any in number, here 4 values are taken).
While making first entry for a day, date is
entered in Column A, i.e. 29-10-07 in this case.
Rest cells in column A remain blank for that day.

As next day dawns, first entry is put in along
with next date, i.e. 30-10-07 in this case.

Our requirement is, as soon as next date is entered,
in this example in row 5, Column C should know that
B1:B4 are to be summed up, and displayed in C4.

The location of C column may be in same worksheet,
or may be in another file, or another computer.

I'm sorry for putting a simple question in complicated way.
Is there any function or combination of functions to achieve above?
I shall go through the solution provided by you in a day or two, and shall
revert
back.

thanks for help !

 

[Guest]

One method is to use a worksheet change function. the function must go on
the VBA worksheet and not a m,odule. Go to excel; spreadsheet and right
click tab on bottom of the page where your data is located. then select view
code. Paste function below on VBA page.

Code looks for any change in column A and then recalculates the entire
column c placing a sum function in column C as required. This was the
fool-proof method of making sure ther was not errors. I considered somebody
typing a date in the wrong row and then having to delete the date. Wanted to
make usre column C got corrected.



Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If cell.Column = 1 Then
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

FirstRow = 1
For RowCount = 1 To LastRow
If (Cells(RowCount + 1, "A") <> "") Or _
(RowCount = LastRow) Then

Cells(RowCount, "C").Formula = "=sum(B" & _
FirstRow & ":B" & RowCount & ")"
FirstRow = RowCount + 1
Else
Cells(RowCount, "C") = ""
End If
Next RowCount
End If

Next cell

End Sub