☰

如何从上一张表格创建数字 +1

tag-icon • tag-icon microsoft-excel worksheet-function vba
如何从上一张表格创建数字 +1

我想使用宏将单张工作表复制到同一工作簿中的多张工作表，我知道如何操作，但我还有其他问题。

我正在使用公式：

Sub Button3_Click()
Dim x As Integer
Dim y As Integer
x = InputBox("How many copies you want?")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub

假设我将单张纸复制到 5 张新纸中，纸的名称将是：

Sheet 1 | Sheet 1 (2) | Sheet 1 (3) | Sheet 1 (4) | Sheet 1 (5) | Sheet 1 (6)

我想要的是：

the formula in the "Sheet 1 (2)" Cell A1 is "='Sheet 1'A1+1" and 
the formula in the "Sheet 1 (3) cell A1 is "='Sheet 1 (2)'A1+1: and 
the formula in the "Sheet 1 (4) cell A1 is "='Sheet 1 (3)'A1+1: and
the formula in the "Sheet 1 (5) cell A1 is "='Sheet 1 (4)'A1+1: and

或者无论公式是什么，我只希望下一个工作表单元格 A1 引用上一个工作表中的 +1 数字。（就像页面一样）。

我想知道该如何做，特别是在 excel 2003 中？

答案1

在Sheet 1 (2)输入A1​

=SUM(Sheet 1!A1,1)

并分别到其他工作表。

对于 LibreOffice 来说

=SUM(Sheet 1.A1,1)

答案2

尝试在您的代码中添加以下内容：

Sub Button3_Click()
Dim x As Integer
Dim y As Integer, tmpSheet As Worksheet
x = InputBox("How many copies you want?")
For numtimes = 1 To x
    ActiveWorkbook.Sheets("Sheet1").Copy _
    After:=ActiveWorkbook.Sheets("Sheet1")
Next
For y = 2 To x + 1
    Set tmpSheet = ActiveWorkbook.Sheets("Sheet1 (" & y & ")")
    If y = 2 Then
        tmpSheet.Range("A1").Formula = "='Sheet1'!A1 + 1"
    Else
        tmpSheet.Range("A1").Formula = "='Sheet1 (" & y - 1 & ")'!A1 + 1"
    End If
Next y
End Sub

相关内容