Good Morning,
My team and I track the amount of water hauled from one location to another, complete with water type, trucking company, volume hauled etc. Each ws is specific to the offload location. In the following code, I am trying to insert a SUMIFS formula into the appropriate cell that dynamically links to the correct table and column for a "Dashboard" master type worksheet. A separate Module contains code that creates a new ws and inserts a named table on new ws with the same sheet name passed from a userform. I am attempting to reference the string variable to designate which table to pull from and insert that value in front of the column name in the formula. The column names for the referenced sheet are standard and the user can simply change the date (referenced in R[-1],C[8] to see the different sums on the dashboard, which are then copied and pasted into a daily email. After much trial and error, I keep receiving a run-time error 1004. Any help with the following syntax or suggestions is greatly appreciated! ...or is my approach all wrong?
Dim strNewNameF as String
strNewNameF = UserForm2.TextBox1.Value
With ws.Range("A21:B21")
.Merge
.Font.name = "Calibri"
.Font.Size = 11
.HorizontalAlignment = xlCenter
.BorderAround Weight:=xlMedium
.FormulaR1C1 = "=SUMIFS( """ & strNewNameF & """""[Volume Hauled (bbls)]"" & , """ & strNewNameF & """""[Date]"" & ,R[-1]C[8], & """ & strNewNameF & """""[Water Type]"" & ,""Production"")"
End With