Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto fill data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto fill data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto fill data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto fill data
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto fill data
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 ! "Joel" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto fill data
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 "hsg" wrote: 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 ! "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to Auto Fill columns with data | Excel Discussion (Misc queries) | |||
auto fill data from cells | New Users to Excel | |||
Auto data fill in | Excel Worksheet Functions | |||
Auto fill for data from another worksheet | Excel Discussion (Misc queries) | |||
auto fill sequancial data | Excel Discussion (Misc queries) |