☰

如何输入一个公式来从 Excel 工作簿中新添加的工作表（不是由数字指定的）中提取数据？

tag-icon • tag-icon microsoft-excel worksheet-function vba
如何输入一个公式来从 Excel 工作簿中新添加的工作表（不是由数字指定的）中提取数据？

我的 Excel 工作簿有一个摘要 Recap 表，后面跟着许多表，每个表都以不同的城市命名。随着时间的推移，越来越多的城市（和表）被添加，因此我在 Recap 表中添加了更多行。我正在尝试创建一个宏，用新添加的城市表中的数据填充新 Recap 行中的单元格。

在宏中，如何输入从工作簿新添加的工作表而不是特定工作表标题或工作表编号中提取数据的公式？

例如，我最近添加的城市（和工作表）是波士顿，但现在我需要添加更多城市。

迄今为止的宏观情况：

Sub AddToRecap()
'
' AddToRecap Macro
' Add new city info to Recap sheet
'

'
    Range("B39").Select
    ActiveCell.FormulaR1C1 = "=Boston!R[-30]C"
    Range("A40:C40").Select
End Sub

我尝试过改变或Boston! 的各种排列，但都不起作用。(Sheets.Count)Sheets(Sheets.Count).Select

答案1

非常粗略和基础，这将产生最后一个工作表名称。

Sub LastSheetName()
    Dim wbkBook As Excel.Workbook

    Set wbkBook = Excel.ActiveWorkbook
    Debug.Print wbkBook.Worksheets(wbkBook.Worksheets.Count).Name
End Sub

工作表名称就是您需要引用它的名称。

修订编辑：

Function GetLastSheetName() As String
    Dim wbkBook As Excel.Workbook

    Set wbkBook = Excel.ActiveWorkbook
    GetLastSheetName = wbkBook.Worksheets(wbkBook.Worksheets.Count).Name
End Function

使用此函数，或将其分配给代码中的字符串变量。结合 Engineer Toast 的回答/评论，您应该可以让它工作。

Engineering Toast 的注释：

ActiveCell.FormulaR1C1 = "='" & wbkBook.Worksheets(wbkBook.Worksheets.Count).Name & "'!R[-30]C"

尝试这个

Dim strLastSheetName as String

strLastSheetName = GetLastSheetName()

ActiveCell.FormulaR1C1 = "='" & strLastSheetName & "'!R[-30]C"

答案2

正如其他人所说，如果您希望纯 Excel 工作表公式引用另一张工作表上的单元格，则该公式需要包含 姓名引用工作表的公式，不管怎样。但是，如果你愿意使用 VBA 增强公式，使用 VBA功能， 你可以做一些你更喜欢的事情。

此函数将允许您使用工作表编号访问（读取）另一张工作表上的单元格：

Function abs_ref(sheet_num, ref)
    abs_ref = Sheets(sheet_num).Range(ref.Address)
End Function

.Value赋值行末尾有一个隐式表达式。.FormulaR1C1如果愿意，您可以在此处添加显式表达式。冒着过于简单化的风险， .Value将从远程工作表中获取求值值，而.FormulaR1C1 将从远程工作表中检索未求值的公式并在本地工作表上求值。如果您能提出一个好问题，我可以解释更多。

从这里开始，这实际上与 VBA 无关。如果您希望单元格B39（在当前工作表上）引用Boston!B9，Boston即第 42 个工作表，您只需设置B39为=abs_ref(42, B9)。如果您想从 VBA 执行此操作，请执行

    Range("B39").FormulaR1C1 = "=abs_ref(" & Sheets.Count & ", R[-30]C)"

相关内容