使用VBA副本表宏和公式r1c1

2 回答

• 0

  从我的帖子中可以理解，下面的代码将根据用户在 InputBox 中选择的次数运行，并在最后一次打开后复制一张Sheet .

  对于每个创建的工作表，它会将公式添加到 Cell C4 ，我只是不确定推进每个工作表的公式的逻辑 .

  Sub CopySheets()
  
  Dim x           As Long
  Dim numtimes    As Long
  Dim newSht      As Worksheet
  
  
  x = Application.InputBox("Enter number of times to copy active sheet", Default:=1, Type:=1)
  
  ' optimize run time
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  Application.DisplayAlerts = False
  
  ' create the Buttons on the original sheet
  ' (will be copied inside the loop for all other sheets)
  ActiveSheet.Buttons.Add(541.5, 97.5, 95.25, 43.5).Select
  ActiveSheet.Buttons.Add(541.5, 169.5, 94.5, 42.75).Select
  
  For numtimes = 1 To x
  
      'Loop by using x as the index number to make x number copies
       ActiveWorkbook.ActiveSheet.Copy _
              After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
  
      Set newSht = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
  
      ' give the new Sheet Name the reference of num of times
      newSht.Name = "<NULL " & numtimes & ">"
  
      ' advance the row number in the formula
      newSht.Range("C3").FormulaR1C1 = "='Dividing Walls Only'!R[" & numtimes & "1]C[-2]"
  
  Next numtimes
  
  ' Resume Settings
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.DisplayAlerts = True
  
  End Sub
  

  回复于 2024-04-28T19:30:02+08:00
• 0

  可能这就是你所追求的：

  Option Explicit
  
  Sub CopySheet()
      Dim numtimes As Long, x As Long, rowIndex As Long
  
      Call OptimizeCode_Begin
      rowIndex = 4 '<-- this is the row index that will be used in the formula that'll be written in the first new sheet
      numtimes = Application.InputBox("Enter number of times to copy active sheet", Default:=1, Type:=1)
      For x = 1 To numtimes
          'Loop by using x as the index number to make x number copies
          ActiveWorkbook.ActiveSheet.Copy _
          After:=ActiveWorkbook.Sheets(3)
          Range("C3").Formula = "='Dividing Walls Only'!A" & rowIndex '<--| write formula in the new sheet cell "C3" referencing "Dividing Walls Only" worksheet column "A" cell in current 'rowIndex'
          rowIndex = rowIndex + 1 '<--| update row index for subsequent new sheet formula
      Next
      Call OptimizeCode_End
  End Sub
  

  你看我使用Excel（即 Application ）InputBox()方法而不是VBA InputBox()，因为前者允许你指定返回数据类型（类型：= 1表示数字输入），因此强制用户输入到想要的输入 .

  回复于 2024-04-28T19:30:02+08:00