Hello,
I have written a macro to split a large table into several smaller ones on separate worksheets. Once this is done, I want to put the same two sumif formulae in the same cell in each sheet. My current code, which works, uses columns as references:
Code:
For Each ws In Sheets
ws.Activate
Range("A1:A6").EntireRow.Insert
Range("F3").Formula = _
"=sumif(E:E,""<>Translation"",H:H)"
Range("F4").Formula = _
"=sumif(E:E,""Translation"",H:H)"
Range("E3").Value = "Proofreading"
Range("E4").Value = "Translation"
With Range("E3:F4")
.Font.Bold = True
.HorizontalAlignment = xlCenter
.BorderAround Weight:=xlMedium
End With
Range("A1").Select
Next ws
However, I would like to be able to reference the named range within the table, the formula for which (based on a recorded macro) would be something like:
Code:
Range("F3").FormulaR1C1 = _
"=SUMIF(TABLENAME[[#All],[Issue Type]],""<>Translation"",TABLENAME[[#All],[Word count]])"
Is it possible to define a variable, such as ActiveSheet.ListObjects(1), and inserting the variable into the formula so that when the loop goes to each sheet, it does the calculation based on the table in that sheet (replacing the placeholder "TABLENAME" in my example code? I've tried several different things and none seem to work. If it's of any use, I've made it so the table on each sheet has the same name as the sheet.
Thanks in advance.