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.
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
Many thanks for your help.