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.

"hsg" wrote:

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.

"Joel" wrote:


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) & _
":D" & 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

"hsg" wrote:

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