在 Excel 中使用 VBA 复制或移动工作表

您所在的位置：网站首页 › 如何将工作表复制到工作簿的最后 › 在 Excel 中使用 VBA 复制或移动工作表

在 Excel 中使用 VBA 复制或移动工作表

2024-07-16 22:35| 来源: 网络整理| 查看: 265

1.在活动工作簿中复制特定的工作表 Sub Copier1() 'Replace "Sheet1" with the name of the sheet to be copied. ActiveWorkbook.Sheets("Sheet1").Copy _ after:=ActiveWorkbook.Sheets("Sheet1") End Sub

2.在活动工作簿中多个时间复制特定的工作表

Sub Copier2() Dim x As Integer x = InputBox("Enter number of times to copy Sheet1") For numtimes = 1 To x 'Loop by using x as the index number to make x number copies. 'Replace "Sheet1" with the name of the sheet to be copied. ActiveWorkbook.Sheets("Sheet1").Copy _ After:=ActiveWorkbook.Sheets("Sheet1") Next End Sub

3.复制 ActiveSheet 多时间

Sub Copier3() Dim x As Integer x = InputBox("Enter number of times to copy active sheet") For numtimes = 1 To x 'Loop by using x as the index number to make x number copies. ActiveWorkbook.ActiveSheet.Copy _ Before:=ActiveWorkbook.Sheets("Sheet1") 'Put copies in front of Sheet1. 'Replace "Sheet1" with sheet name that you want. Next End Sub

4.一次复制一个工作簿中的全部工作表

Sub Copier4() Dim x As Integer For x = 1 To ActiveWorkbook.Sheets.Count 'Loop through each of the sheets in the workbook 'by using x as the sheet index number. ActiveWorkbook.Sheets(x).Copy _ After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) 'Puts all copies after the last existing sheet. Next End Sub

5.将活动工作表移动到工作簿中新位置

Sub Mover1() ActiveSheet.Move _ After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) 'Moves active sheet to end of active workbook. End Sub

6.将活动工作表移动到另一工作簿

Sub Mover2() ActiveSheet.Move Before:=Workbooks("Test.xls").Sheets(1) 'Moves active sheet to beginning of named workbook. 'Replace Test.xls with the full name of the target workbook you want. End Sub

7.在 ActiveWorkbook 中的多个表移动到另一工作簿

Sub Mover3() Dim BkName As String Dim NumSht As Integer Dim BegSht As Integer 'Starts with second sheet - replace with index number of starting sheet. BegSht = 2 'Moves two sheets - replace with number of sheets to move. NumSht = 2 BkName = ActiveWorkbook.Name For x = 1 To NumSht 'Moves second sheet in source to front of designated workbook. Workbooks(BkName).Sheets(BegSht).Move _ Before:=Workbooks("Test.xls").Sheets(1) 'In each loop, the next sheet in line becomes indexed as number 2. 'Replace Test.xls with the full name of the target workbook you want. Next End Sub

【本文地址】

在 Excel 中使用 VBA 复制或移动工作表

在 Excel 中使用 VBA 复制或移动工作表

今日新闻

推荐新闻