Hello All,
This query continues on from a previous post: http://www.ozgrid.com/forum/showthread.php?t=204475 which has been answered perfectly.
The position of the last column and the number of columns that are totalled may change.
Sumifs Range: Cells(i,11), Cells (i,[COLOR="#FF0000"]20[/COLOR]) - i would like to replace 20 with the last column
Paste Range: Range("U" & i & ":Y" & i) - this would be the last column + 1 to totrange (or last column +5 cols)
I have tried posting the variable name into the code (which was suggested elsewhere) but get an error on both lines.
Code
Sub Totals()
Dim lrow As Long
Dim i As Long
Dim j As Integer
Dim lcol As Long
Dim totrange As Long
lrow = Cells(Rows.Count, 1).End(xlUp).Row
lcol = Range("z49").End(xlToLeft).Column
totrange = lcol + 5
For i = 11 To lrow
For j = lcol To totrange
If Cells(i, 11) <> "Units" Then
Cells(i, j) = WorksheetFunction.SumIfs(Range(Cells(i, 11), Cells(i, 20)), Range("K10:T10"), Cells(10, j))
Else
Cells(i, j) = Cells(i, j - 5)
End If
Next j
Range("k" & i & ":O" & i).Copy
Range("U" & i & ":Y" & i).PasteSpecial Paste:=xlPasteFormats
If Range("l" & i) = 0 Or Len(Range("l" & i)) = 0 Then Range("K" & i & ":O" & i) = ""
Next i
Display More
Many thanks for your help.