时间戳

首页 > 解决方案 > 当“最后一张”每月更改时，如何将“最后一张”中的单元格引用（不是值）添加到汇总表公式

excel - 当“最后一张”每月更改时，如何将“最后一张”中的单元格引用（不是值）添加到汇总表公式

问题描述

我正在自动化会计工作表（WS）。[一个工作簿，一个贡献者]

每个月他们都会在那个月添加一个新的 WS。[表单控制按钮]

Sheets(Sheets.Count).Select 
Cells.Select
Selection.Copy
Range("A1").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
ActiveSheet.Select
ActiveSheet.Name = Format$(Application.WorksheetFunction.EoMonth(Now(), -1), "mm-yy")

我需要将新 WS 中的单元格引用添加到摘要表中。引用需要根据他们在上一个 WS 中所做的更改进行更新。

他们要求我将“最后一张表”中的单元格引用添加到摘要表中的公式中。

[汇总表的结果已分发。]

请帮忙。谢谢你。

标签： excelvba

解决方案

目标：在 VBA 程序添加当月工作表后，有一个更新合并工作表公式的程序。

假设：

  • 月份工作表和合并工作表具有相同的结构，即：将位于单元格中的月份工作表中的值E19添加到E19合并工作表的单元格中。换句话说，公式："=+'01-21'!E19+'02-21'!E19+'03-21'!E19+'04-21'!E19+'05-21'!E19"进入E19合并工作表的单元格。

方法建议：

  • 不使用具有绝对引用的公式。
  • 使用Range.FormulaR1C1 属性 (Excel)输入公式。
  • 定义一个常量来保存要更新的范围的地址（OP 提供的范围包含 69 个单元格）。
  • 定义一个常量来保存合并公式的“模板”。
  • 获取待处理月份后，对公式模板进行裁剪更新，得到该月份对应的公式。
  • 使用上一步中获得的 R1C1 公式更新公式范围。

这是程序：

Sub Formulas_Update_Month(Wsh As Worksheet)

Const kRng As String = "E19:E24,E30:E35,E41,E47,E50:E51,E54:E55,E58,E60,E63:E64,E67,E69,E71," & _
                        "G19:G24,G30:G35,G41,G47,G50:G51,G54:G55,G58,G60,G63:G64,G67,G69,G71," & _
                        "X19:X22,X41:X44,X46,X50:X51,X54:X55,X58:X60,X63:X64,X67"
Const kFml As String = "= +'01-#YY'!RC +'02-#YY'!RC +'03-#YY'!RC +'04-#YY'!RC" & _
                        " +'05-#YY'!RC +'06-#YY'!RC +'07-#YY'!RC +'08-#YY'!RC" & _
                        " +'09-#YY'!RC +'10-#YY'!RC +'11-#YY'!RC +'12-#YY'!RC"
Dim rFmls As Range, dDate As Date, sFml As String

    Rem Set Actual Month
    dDate = Application.EoMonth(Now, -1)
    
    Rem Set Formulas Range
    Set rFmls = Wsh.Range(kRng)

    Rem Set Formula
    sFml = kFml
    If Month(dDate) < 12 Then sFml = Left(sFml, -1 + InStr(sFml, Format(Month(1 + dDate), "+'00")))
    sFml = Replace(sFml, "#YY", Format(dDate, "YY"))

    Rem Update Formula Range
    rArea.FormulaR1C1 = sFml
    
    End Sub

应该从复制新月份工作表的过程中调用它，如下所示：

Call Formulas_Update_Month(Wsh)

Wsh该过程创建的工作表在哪里New Month。

推荐阅读