Thanx Nigel

I works fine except for one small detail. When I run the macro for
second time it gives me problems. I think this is becuase I do a fil
function and I have hardcoded the destination fill. Does that mak
sense to you?


Code
-------------------
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(lastrow + 1, 1) = "TOTAL"
Cells(lastrow + 1, 2).Formula = "=sum(B3:B" & lastrow & ")"

'format totals
Cells(lastrow + 1, 2).Select
Selection.AutoFill Destination:=Range("B156:K156"), Type:=xlFillDefault ' I think the problem is here. I need to have a
variable that will change according to number of rows

Rows("156:156").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("B3:O157").Select
Selection.Replace What:="", Replacement:="0.00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N").Select
Selection.NumberFormat = "0"
Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O").Select
Selection.NumberFormat = "#,##0.00"


-------------------


Thanx agai

--
Message posted from http://www.ExcelForum.com

I guess two points

1. If you run the macro twice it will put the total in twice, since the code
to find the last row will be moved on to the total created in the first
run - not a good idea!

2. Your fill is OK just change the reference to the lastrow variable, so in
the part where you specify the range use

Range("B" & lastrow & ":K" & lastrow)

or use to allow control of columns using a loop or variable (if required)

Range(Cells(ir, 2), Cells(ir, 11))

Cheers
Nigel

"poppy " wrote in message
...
Thanx Nigel

I works fine except for one small detail. When I run the macro for a
second time it gives me problems. I think this is becuase I do a fill
function and I have hardcoded the destination fill. Does that make
sense to you?


Code:
--------------------
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Cells(lastrow + 1, 1) = "TOTAL"
Cells(lastrow + 1, 2).Formula = "=sum(B3:B" & lastrow & ")"

'format totals
Cells(lastrow + 1, 2).Select
Selection.AutoFill Destination:=Range("B156:K156"), Type:=xlFillDefault
' I think the problem is here. I need to have a
variable that will change according to number of rows

Rows("156:156").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("B3:O157").Select
Selection.Replace What:="", Replacement:="0.00", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N").Select
Selection.NumberFormat = "0"
Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O").Select
Selection.NumberFormat = "#,##0.00"


--------------------


Thanx again


---
Message posted from http://www.ExcelForum.com/