将整个工作表复制到Excel 2010中的新工作表

6 回答

• 2

  我真的很喜欢@brettdj 's code, but then I found that when I added additional code to edit the copy, it overwrote my original sheet instead. I'已经调整了他的答案，以便进一步的代码指向 ws1 将影响新的工作表而不是原始工作表 .

  Sub Test()
      Dim ws1 as Worksheet
      ThisWorkbook.Worksheets("Master").Copy
      Set ws1 = ThisWorkbook.Worksheets("Master (2)")
  End Sub
  

  回复于 2024-04-26T04:41:59+08:00
• 36

  'Make the excel file that runs the software the active workbook
  ThisWorkbook.Activate
  
  'The first sheet used as a temporary place to hold the data 
  ThisWorkbook.Worksheets(1).Cells.Copy
  
  'Create a new Excel workbook
  Dim NewCaseFile As Workbook
  Dim strFileName As String
  
  Set NewCaseFile = Workbooks.Add
  With NewCaseFile
      Sheets(1).Select
      Cells(1, 1).Select
  End With
  
  ActiveSheet.Paste
  

  回复于 2024-04-26T04:41:59+08:00
• 17

  只需运行如下所示的精确副本就可以将副本放入最后一页

  Sub Test()
  Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("Master")
  ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
  End Sub
  

  回复于 2024-04-26T04:41:59+08:00
• 1

  ThisWorkbook.Worksheets("Master").Sheet1.Cells.Copy _
      Destination:=newWorksheet.Cells
  

  以上将复制细胞 . 如果你真的想复制整张表，那么我会选择@brettdj's answer .

  回复于 2024-04-26T04:41:59+08:00
• 7

  ' Assume that the code name the worksheet is Sheet1
  
  ' Copy the sheet using code name and put in the end.
  ' Note: Using the code name lets the user rename the worksheet without breaking the VBA code
  Sheet1.Copy After:=Sheets(Sheets.Count)
  
  ' Rename the copied sheet keeping the same name and appending a string " copied"
  ActiveSheet.Name = Sheet1.Name & " copied"
  

  回复于 2024-04-26T04:41:59+08:00
• 0

  如果有人像我一样拥有一个包含默认数量的可见定价表的估算工作簿，一个摘要和大量隐藏的和“受保护的”工作表，其中包含敏感数据，但可能需要创建其他可见的工作表才能获得正确的工作表 . 价格，我有上述响应的变体，基于受保护的隐藏“主”创建所述可见工作表 . 我使用了@ / jean-fran％c3％a7ois-corbett和@ thanos-a与简单VBA组合提供的代码，如下所示 .

  Sub sbInsertWorksheetAfter（）

  'This adds a new visible worksheet after the last visible worksheet
  
      ThisWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
  
      'This copies the content of the HIDDEN "Master" worksheet to the new VISIBLE ActiveSheet just created
  
      ThisWorkbook.Sheets("Master").Cells.Copy _
          Destination:=ActiveSheet.Cells
  
      'This gives the the new ActiveSheet a default name
  
      With ActiveSheet
          .Name = Sheet12.Name & " copied"
      End With
  
      'This changes the name of the ActiveSheet to the user's preference
  
      Dim sheetname As String
  
      With ActiveSheet
          sheetname = InputBox("Enter name of this Worksheet")
          .Name = sheetname
      End With
  

  结束子

  回复于 2024-04-26T04:41:59+08:00