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

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

  <script type="text/javascript"></script>

  • 0
    点赞
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: 下面是将图片移动到图片所在行的最左侧单元格的 VBA 代码: ``` Sub MovePicturesToLeftmostCell() Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets Dim shp As Shape For Each shp In ws.Shapes If shp.Type = msoPicture Then shp.Top = ws.Cells(shp.TopLeftCell.Row, 1).Top shp.Left = ws.Cells(shp.TopLeftCell.Row, 1).Left End If Next shp Next ws End Sub ``` 您可以把代码复制ExcelVBA 编辑器,然后运行它。 ### 回答2: 编写VBA代码实现将当前Excel工作簿所有工作的图片移动到图片所在行的最左侧单元格的操作如下: ```vba Sub MovePicturesToLeft() Dim ws As Worksheet Dim shp As Shape Dim rng As Range ' 遍历所有工作 For Each ws In ThisWorkbook.Worksheets ' 遍历当前工作的所有形状 For Each shp In ws.Shapes ' 判断形状类型是否为图片 If shp.Type = msoPicture Then Set rng = shp.TopLeftCell.Offset(, 1) ' 移动图片到所在行的最左侧单元格 shp.Left = rng.Left shp.Top = rng.Top End If Next shp Next ws End Sub ``` 以上代码通过使用VBA的循环语句和条件判断语句,遍历所有工作,再遍历每个工作的所有形状,判断形状类型是否为图片。如果形状类型为图片,则将该图片移动到所在行的最左侧单元格。具体实现是通过设置图片的Left和Top属性来改变图片的位置,将其移动到所在行的最左侧单元格。 ### 回答3: 在编写VBA代码之前,需要了解以下几点: 1. VBA(Visual Basic for Applications)是一种编程语言,用于在Office应用程序编写宏和自定义功能。 2. Excel的图片是通过插入形状对象来创建的。 3. 工作簿包含多个工作,可以通过`Sheets`或`Worksheets`对象访问它们。 以下是将当前Excel工作簿所有工作的图片移动到图片所在行的最左侧单元格的VBA代码示例: ```vba Sub MovePicturesToLeft() Dim ws As Worksheet Dim shp As Shape For Each ws In ThisWorkbook.Sheets ' 遍历每个工作 For Each shp In ws.Shapes ' 遍历每个工作的形状对象 If shp.Type = msoPicture Then ' 只处理图片(形状对象类型为图片) ' 移动图片到所在行的最左侧单元格 shp.Left = ws.Cells(shp.TopLeftCell.Row, 1).Left End If Next shp Next ws End Sub ``` 解释: 1. `Sub MovePicturesToLeft()`:声明一个子过程(宏),名称为MovePicturesToLeft。 2. `Dim ws As Worksheet`和`Dim shp As Shape`:声明两个变量ws和shp,用于引用工作和形状对象。 3. `For Each ws In ThisWorkbook.Sheets`:遍历当前工作簿的每个工作。 4. `For Each shp In ws.Shapes`:遍历当前工作的每个形状对象。 5. `If shp.Type = msoPicture Then`:判断当前形状对象是否为图片。 6. `shp.Left = ws.Cells(shp.TopLeftCell.Row, 1).Left`:将图片的左边缘移动到图片所在行的最左侧单元格的左边缘。 可使用以下步骤在Excel运行此代码: 1. 按下`Alt + F11`打开Visual Basic编辑器。 2. 在项目资源管理器(通常在左上角),双击要修改的工作簿。 3. 在代码窗口粘贴上面的代码。 4. 按下`F5`运行代码,或按下绿色三角形按钮。 该代码将遍历工作簿的每个工作,并将每个图片移动到其所在行的最左侧单元格。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值