ANY HELP GREATLY APPRECIATED......

[tuggers]

Hi gang,

I work in a dairy and part of my job involves working in the gatehouse where we book tankers in and record there details.

The following is a sheet we use that i would like to improve if at all possible.
basically all the data is gained manually from tanker paperwork and weighbridge dockets. This is entered into the respective columns and the difference calculated in a third column. Then at the end of the shift the columns are totalled up (these can consist of 1 row or 100 rows)
The volumes created by the inputted data are then manually checked against another guaranteed source and the difference between the two recorded along with the totals.
Then finally on another part of the sheet we produce another calculation to show any milk that began pumping before and finished after midnight and the volume that was pumped after midnight is shown so it can be added to the following days sheet.
Has anybody got any ideas how it could be improved?
It currently requires a lot of copy and paste to get things into position, it would be great if a macro could be used for the arrangement and maybe some kind of conditional formatting may help?
Im a bit new to excel but learning fast, i really would appreciate your expert input.

 

[tuggers]

weighbridge reconciliation Jan 2005.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Supplier	Docket	W/bridge	BC55	Diff	Route	Collected						B/F from previous day column M
2	DFOB	111151	16,868	16,857	-11	203	30/01/2005					Total B/F	83500
3	FMILK	111152	28,004	27,995	-9		30/01/2005
4	DFOB	111153	27,162	27,109	-53		30/01/2005		Docket	Silo No.	Silo Allocation	Silo Allocation	Pumped next day
5	DFOB	111154	27,859	27,942	83		30/01/2005		111154	6	26968		26,968
6	DFOB	111155	28,421	28,481	60		30/01/2005		111155				28,421
7	DFOB	111156	28,111	28,052	-59		30/01/2005		111156				28,111
8	DFOB	111159	27,830	27,807	-23	207	30/01/2005				C/F Total to next day column M		83,500
9
10		TOTALS	184,255	184,243	-12
11	net litres	Qualtrace	184,254		0
12		Diff	1
31

 

[tuggers]

I forgot to add, at the end of the reconciliation procedure the required area is selected and printed.
Maybe some way of automatically printing the desired area??

 

[dgpenny]

As a suggestion, copy the sheet to another sheet for working on it. Record a macro doing what you want, then view the macro code. I do that often, then tweaking the code for more of what I want. Then assign it to a macro button and voila!

 

[tuggers]

Thats great advice, thanks a lot.

The trouble is im aware of how to record a macro but not too clever on anything to do with vba.....

 

[Barrie Davidson]

The trouble is im aware of how to record a macro but not too clever on anything to do with vba.....

Post your recorded macro and add what changes you need. Somebody will help you out.

Regards,

 

[tuggers]

there are 3 rows of entry under the lists of figures containing totals etc.

My question is, can a macro be created to make that appear and do the total calculations under the last entry?

The problem is, the amount of rows changes every day and theres no way of knowing how many there will be, is there any way around this?

 

[Barrie Davidson]

My question is, can a macro be created to make that appear and do the total calculations under the last entry?

The problem is, the amount of rows changes every day and theres no way of knowing how many there will be, is there any way around this?

That's not a problem. Record your macro and post it. We can make the necessary changes to accomodate changing rows.

 

[tuggers]

I have recorded a macro to total the columns required after creating the relevant text in the relevant cells.
The problems i have are

1) The first part of the macro places the text "TOTALS" in a cell, but this is placed in the active cell wherever that is on the sheet and not in the required cell!!

2) I need to get the macro to place the relevant text and totals under the last entry and not in the cells i created it in.

the text should be placed as in the image below, but relevant to the last row produced, can anyone please help?

reconciliation test.xls
	A	B	C	D	E	F	G
1	Supplier	Docket	W/bridge	BC55	Diff	Route	Collected
2	DFOB	111151	16,868	16,868	0	203	30/01/2005
3	FMILK	111152	28,004	27,995	-9		30/01/2005
4	DFOB	111153	27,162	27,109	-53		30/01/2005
5	DFOB	111154	27,859	27,942	83		30/01/2005
6	DFOB	111155	28,421	28,481	60		30/01/2005
7	DFOB	111156	28,111	28,052	-59		30/01/2005
8	DFOB	111159	27,830	27,807	-23	207	30/01/2005
9	FMILK	111167	27,966	28,068	102		30/01/2005
10
11		TOTALS	212,221	212,322	101
12	net litres	Qualtrace
13		Diff	212,221
Sheet1

 

[tuggers]

Here is the code created for the macro.

Sub totals()
'
' totals Macro
' Macro recorded 21/12/2005 by Dave
'
' Keyboard Shortcut: Ctrl+t
'
ActiveCell.FormulaR1C1 = "TOTALS"
Range("B12").Select
ActiveCell.FormulaR1C1 = "Qualtrace"
Range("B13").Select
ActiveCell.FormulaR1C1 = "Diff"
Range("A12").Select
ActiveCell.FormulaR1C1 = "net litres"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=R[-2]C-R[-1]C"
Range("C13").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A11:E13").Select
Selection.Font.Bold = True
Range("C16").Select
Columns("B:B").EntireColumn.AutoFit
Range("C2:C11").Select
Range("C11").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("D2:D11").Select
Range("D11").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("D2:D11").Select
End Sub