Hello, Gurus.
I am trying to enter a sum formula in cell T2 that sums all data from U7 to the end of the column. The end of the column is dynamic, and at the bottom of the column there is a blank row folowed by a "TOTALS" row that I don't want included in the sum. I tried this knowing it wouldn't work, but though this may give you an idea of what I am trying to do.
Range("T2").FormulaR1C1 = "Sum(Range("U7", Range("U7").End(xlDown)))
One other thing that may confuse me is that the formula is within a "With Sheets" statement. Here is the full section of code, with the bad formula commented out.
With Sheets("ALL PARTS")
Sheets.Add().Name = "Temp"
.Range("L6", .Range("L6").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
Set rng = Sheets("Temp").Range("A2", Sheets("Temp").Range("A2").End(xlDown))
For Each rng2 In rng
.Range("A6").CurrentRegion.AutoFilter Field:=12, Criteria1:=rng2
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
.AutoFilter.Range.Resize(.AutoFilter.Range.Rows.Count + 2).Copy ws.Range("A6")
.Rows("1:5").Copy
Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rows("7:7").Select
ActiveWindow.FreezePanes = True
'Range("T2").FormulaR1C1 = "Sum(Range("U7", Range("U7").End(xlDown)))
On Error Resume Next
Sheets("" & rng2 & "").Delete
On Error GoTo 0
ws.Name = rng2
.Range("A6").CurrentRegion.AutoFilter Field:=12
Next rng2
Sheets("Temp").Delete
.AutoFilterMode = False
End With
Bookmarks