VBA: Referring to a table name variable within formula code

p45cal · #2 08-02-2022, 11:48 AM

If there's only 1 table on each sheet, try:

Code:

For Each ws In Sheets
  With ws
    TblName = .ListObjects(1).Name
    .Range("A1:A6").EntireRow.Insert
    .Range("F3").FormulaR1C1 = "=SUMIF(" & TblName & "[Issue Type],""<>Translation""," & TblName & "[Word Count])"
    .Range("F4").FormulaR1C1 = "=SUMIF(" & TblName & "[Issue Type],""Translation""," & TblName & "[Word Count])"
    .Range("E3").Value = "Proofreading"
    .Range("E4").Value = "Translation"
    With .Range("E3:F4")
      .Font.Bold = True
      .HorizontalAlignment = xlCenter
      .BorderAround Weight:=xlMedium
    End With
  End With
Next ws

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Code to find a named (bookmarked?) table, replicate a row or table, and delete a specified table.	kevinbradley57	Word VBA	9	09-21-2017 04:58 PM
Variable data set in Average formula	Filip88	Excel	12	09-17-2017 06:54 AM
Is there code for variable fill down in excel	jtp607	Excel	4	06-08-2017 11:50 AM
Variable text field code based on occurrences on each page	Cosmo	Word	2	12-29-2015 11:54 AM
Filling a range variable into a formula	Officer_Bierschnitt	Excel Programming	3	11-23-2015 03:30 AM