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]

Also, yet another query.
I dont know if this is possible but is there a way that the area of a sheet that contains text/data could be selected for printing and the print format changed depending on the best fit for a page??

 

[Barrie Davidson]

Does this do what you need?

Option Explicit

Sub Totals()
Dim LastRow As Long

LastRow = Range("G1").End(xlDown).Row
Range("B" & LastRow + 2) = "TOTALS"
Range("B" & LastRow + 3) = "Qualtrace"
Range("B" & LastRow + 4) = "Diff"
Range("A" & LastRow + 3) = "net litres"
Range("C" & LastRow + 2 & ":D" & LastRow + 2).Formula = "=SUM(C2:C" & LastRow & ")"
Range("E" & LastRow + 2).FormulaR1C1 = "=RC[-1]-RC[-2]"
With Range("C" & LastRow + 4)
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Range("E" & LastRow + 2)
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Range("A" & LastRow + 2 & ":E" & LastRow + 4).Font.Bold = True
Columns("A:D").EntireColumn.AutoFit
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & LastRow + 4
With ActiveSheet.PageSetup
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With


End Sub

 

[tuggers]

THANKS A MILLION FOR THE HELP GUYS.

I used the above code but came across a problem (i think that i created)
I inserted a few rows at the top of the worksheet to place a button to run the macro, once i had assigned the code to the button, when clicked, it places the results in the wrong place!!??

It places it in rows 7, 8 and 9 when the last entry is on row 13.

Any idea of a way round this?

Also i would like to assign the print area code to a button as well so is there any chance you could show me that code seperately?
Also i apologise for not explaining properly previously but the print area needs to cover the whole sheet area which goes right across to row N.

Again, many thanks

 

[tuggers]

OK

Ive sorted the positioning issue by removing the inserted rows and placing the button elsewhere on the sheet - works a treat.

The only problem with it is, there is a calculation missing.

The cell next to the 'Diff' one should minus the 'TOTALS' amount from whatever amount is placed in the cell below it, but this isnt produced using the macro.

 

[tuggers]

Alright, ive also sorted the range for the print area
(im gettin quite good at this stuff !! lol)
and ive solved the problem with the cell calculation.

THE ONLY THING I NOW NEED IS THE ABILITY TO HAVE A SEPERATE CODE FOR SETTING PRINT AREA AND SELECTING BEST FIT FOR THAT AREA.
This may seem simple but i cant work it out, also im assuming once this is done its just a matter of hitting print and no other settings need adjusting?

can anyone help?

 

[tuggers]

Also,

This sheet needs to be copied across 31 sheets in the same workbook to cover a calendar month.
How do i manage this as i cant get the button i created to copy across without losing the macro assigned to it.

This would need to be done every month, a new blank workbook would need to be created from a template, containing 31 sheets. As several different people may be doing this job, is there any way to assign a macro to do this?

 

[tuggers]

Can anyone please help??

 

[tuggers]

Anyone????

Please????

 

[Barrie Davidson]

...
THE ONLY THING I NOW NEED IS THE ABILITY TO HAVE A SEPERATE CODE FOR SETTING PRINT AREA AND SELECTING BEST FIT FOR THAT AREA.
...

Do you mean you want to print on one page? How do you determine "best fit"?

Also,

This sheet needs to be copied across 31 sheets in the same workbook to cover a calendar month.
How do i manage this as i cant get the button i created to copy across without losing the macro assigned to it.

This would need to be done every month, a new blank workbook would need to be created from a template, containing 31 sheets. As several different people may be doing this job, is there any way to assign a macro to do this?

How did you assign the button? Try creating the button from the Forms menu and assign the macro that way. You can copy the sheet and not lose the macro.

Regards,