Automatically update column - Autofill

[poppy]

Good Day ladies and Gents

I have two tiny problems and maybe you can help me.

1. First Problem: This piece of code is suppose to find the last ro
without data and give me the totals of all the columns in th
spreadsheet. The problem is that everytime more data is added, I hav
to go to my code and change the value in red - cause it is hardcoded
Eg if the last populated column is G and new data is added on to colum
J, I have to change G to J.


Code
-------------------
'find the last empty row and insert totals
lastrow = Cells(Rows.Count, "B").End(xlUp).row
Cells(lastrow + 1, 1) = "TOTAL"
Cells(lastrow + 1, 2).Formula = "=sum(B3:B" & lastrow & ")"

'format totals
lastrow = Cells(Rows.Count, "B").End(xlUp).row
Cells(lastrow, 2).Select
Selection.AutoFill Destination:=Range("B" & lastrow & ":G" & lastrow), Type:=xlFillDefault

lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
'format the lastrow with totals to font 12 and bold
For i = 1 To lastcol
For j = lastrow To lastrow
With Cells((j), (i))
.Font.Bold = True
.Font.Size = 12
End With
Next
Next

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


2. Second problem: I have columns of data for a number of months and a
the end of the sheet I have to make a summary of figures for each month
My problem is how do I determine what the last month is and update th
sheet? Eg, if October's data is added on, how do I get the summary t
be automatically updated?


Code
-------------------
July August September
Product Qty Qty Qty
Hake 0 0 0
Eel 2 0 2
Prawn 56 64 23
Oyster 66 82 42
Roe 24 11 19
Sturgen 2 7 6
Mussels 13 14 11
Calamari 0 2 0
Lobster 530 539 362

July August September
Lobster 530 539 362
Other 163 180 10
-------------------


I have to constantly update this code I'm using. Eg if another month i
added on, I have to change .Resize(,3) TO .Resize(,4) and so on.

Code
-------------------

lastrow = Cells(Rows.Count, "A").End(xlUp).row
lastrow = lastrow + 3

'Fill in the month names
For i = 2 To 2
For j = lastrow To lastrow
With Cells((j), (i))
.FormulaR1C1 = "Jan"
.AutoFill Destination:=.Resize(, 3), Type:=xlFillDefault
End With
Next
Next

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


Please help if you have any ideas for me. Thanx


Kind Regard

 

[Tom Ogilvy]

Switch these two lines and modify as shown:

Selection.AutoFill Destination:=Range("B" & lastrow & ":G" & lastrow),
Type:=xlFillDefault

lastcol = Cells(2, Columns.Count).End(xlToLeft).Column

-------------------------------------
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column

Selection.AutoFill Destination:=Range(Cells(lastRow,2), _
Cells(lastrow,lastcol)), Type:=xlFillDefault

=================================

lastrow = Cells(Rows.Count, "A").End(xlUp).row
lastrow = lastrow + 3

'Fill in the month names
i = 2
j = lastrow
k = Cells(2,"IV").End(xltoLeft).column - 1
With Cells(j, i)
.FormulaR1C1 = "Jan"
.AutoFill Destination:=.Resize(, k), Type:=xlFillDefault
End With


Would be my Guess