excel将形状从一个工作表复制到另一个工作表 [英] excel Copy shapes from one worksheet to another

问题描述

我正在尝试使用宏将所有形状(图像)从工作表复制到另一个.我使用了记录宏来执行此操作，但是它总是为形状赋予一个临时的名称，从而在我们不知道形状名称的情况下无法对其进行复制.

解决方案

这会将所有形状从Sheet1复制到Sheet2:

Sub CopyShape()
    Dim s As Shape
    For Each s In Sheets("Sheet1").Shapes
        s.Copy
        Sheets("Sheet2").Paste
    Next s
End Sub

复制完成后，您可以根据需要放置它们，也可以根据需要对其重命名.
(另一种方法是只复制整个工作表.)

EDIT#1:

此代码还将自动为复制的形状分配名称和位置:

Sub CopyShape()
    Dim shp1 As Shape, nombre As String
    Dim s1 As Worksheet, s2 As Worksheet
    Dim shp2 As Shape

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")

    For Each shp1 In s1.Shapes
            nombre = shp1.Name
            shp1.Copy
            s2.Paste
            Set shp2 = s2.Shapes(s2.Shapes.Count)
            shp2.Name = nombre
            shp2.Top = shp1.Top
            shp2.Left = shp1.Left
    Next shp1
End Sub

如果执行重新复制，请小心避免名称冲突.

I am trying to use a macro to copy all the shapes (images) from a worksheet to another. I used the record macro to do it, but it always gives an aleatory name to the shape making it impossible to reproduce it when we don't know the name of shapes.

解决方案

This will copy all the shapes from Sheet1 to Sheet2:

Sub CopyShape()
    Dim s As Shape
    For Each s In Sheets("Sheet1").Shapes
        s.Copy
        Sheets("Sheet2").Paste
    Next s
End Sub

Once the copy is complete, you can position them as you like or rename them as you like.
(An alternative is just to make a copy of the entire worksheet.)

EDIT#1:

This code will also automatically assign Names and positions to the copied Shapes:

Sub CopyShape()
    Dim shp1 As Shape, nombre As String
    Dim s1 As Worksheet, s2 As Worksheet
    Dim shp2 As Shape

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")

    For Each shp1 In s1.Shapes
            nombre = shp1.Name
            shp1.Copy
            s2.Paste
            Set shp2 = s2.Shapes(s2.Shapes.Count)
            shp2.Name = nombre
            shp2.Top = shp1.Top
            shp2.Left = shp1.Left
    Next shp1
End Sub

Be careful to avoid name conflicts if you perform re-copies.

这篇关于excel将形状从一个工作表复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！