Setting Print Area

[Jmmac04]

Hi All~
I tried searching the board, but could not find exactly what I need.
I have a spreadsheet in which data will be entered into. The number of rows will differ each time it is used. The people using this are not very experienced in excel, so I need to automate things as much as possible. When the user is done entering data, I need to have a sum of column F in the last cell under the last bit of data in F (which will always be a different cell) then to have the print area set from that point to A1.
Any ideas?
Thanks much!

 

[clipro]

Hi

First let column A for the needs of this macro
In cell A2 put the formula
=IF(ISBLANK(B2);"";1)
and in each cell bellow
=IF(ISBLANK(B3);"";A2+1)
IN cell A1 put
=MAX(C9:C22)

after that you can use this macro

few notes the values after > are approximate and depends to you to determinate them

' Print Macro
' Macro recorded by CliPro
'
Sub Print()
If Sheets("Sheet1").Range("A1").Value > 168 Then
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1
ElseIf Sheets("Sheet1").Range("A1").Value > 72 Then
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1
Else
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If
End Sub

I am sure that there is a better way but that is my proposal

 

[OdinsDream]

Here's the code to do what you need, just paste it into a new module, and make a button on your worksheet to run it.

Sub SumIt()
    
    With Cells(1, 6).End(xlDown)
        .Offset(1, 0).FormulaR1C1 = "=SUM(R[-" & .Row & "]C:R[-1]C)"
        ActiveSheet.PageSetup.PrintArea = "$A$1:" & .Offset(1, 0).Address
    End With
    
End Sub

 

[Jmmac04]

Thanks Clipro and OdinsDream!
OdinsDream:
I pasted it into the module and added it. It looks like it is doing something, but it does not do the formula to sum column F...
any ideas?

 

[OdinsDream]

Do you have data in column F, starting at F1? If your data starts lower, you'll need to adjust the line:

With Cells(1, 6).End(xlDown)

to reflect the position. 6 is column "F", and 1 is the row it starts looking on. If your stuff begins on F2, you'll need to have Cells(2, 6).

If your data is not continuous, though... that is you have some blank rows, you'll need to change it to:

With Cells(Rows.Count, 6).End(xlUp)

 

[Jmmac04]

Thanks again OdinsDream:
I see where the problem is. There is a formula in F that is based on data input in cell B. If there is no data in B then the cell has " " in it. The totals were several rows down past that formula. Maybe I am going about this wrong.
I want to have them enter their data in A & B and have the rest populate, then total then set the print area.

Gilhar2.xls
	A	B	C	D	E	F
1	Date	GrossWeight	NetWeight	Tons	PricePerTon	TotalPrice
2	12/10/2004	565656	539456	269.728	$1.95	$525.97
3		456	-25744	-12.872	$1.95	-$25.10
4		65900	39700	19.85	$1.95	$38.71
5	12/10/2004	4554	-21646	-10.823	$1.95	-$21.10
6	12/10/2004	5654845	5628645	2814.323	$1.95	$5,487.93
7		484	-25716	-12.858	$1.95	-$25.07
8		485484	459284	229.642	$1.95	$447.80
9		4849	-21351	-10.6755	$1.95	-$20.82
10		44545445	44519245	22259.62	$1.95	$43,406.26
Sheet1

thanks

 

[OdinsDream]

The second revision I posted should work just fine, even when your data has blanks in it... the one with End(xlUp) in it. Make that change and you should be good to go. If you prefer not having a button to run this, you'll need to put this code in some kind of event, like OnPrint (if such a thing exists) or OnSave.