excel - 避免复制范围内的按钮

Question

我有一个宏，我在其中复制粘贴一个范围，其中也有按钮。现在我不希望按钮被复制。我怎样才能做到这一点？

我复制整个表格并在 A32 再次插入。

lrow = .Cells(Rows.Count, 1).End(xlUp).row
Do While counter = 0
  For i = 32 To lrow
    If .Cells(i, 1).Value = "Review Participants" And counter = 1 Then
     lastrev = lrowrev
     lrowrev = i - 1 'row where the second last review starts
     aboveR = lrowrev - lastrev
     Exit For
    ElseIf .Cells(i, 1).Value = "Review Participants" And counter <> 1 Then
     counter = counter + 1
     lrowrev = i


     lcol = 11  'hardcode last col ~~ Alt:  'lcol = .Cells(i + 1, .Columns.Count).End(xlToLeft).Column 'last meeting of the review is our reference for lastcol
        ElseIf counter = 1 And i = lrow Then
        lrowrev = i + 2
        aboveR = (i + 2) - 32
        Exit For
End If
Next
Loop

lastcolumn = Split(Cells(, lcol).Address, "$")(1)
Set rngtocopy = .Range("A" & 32 & ":" & lastcolumn & lrowrev)

Debug.Print rngtocopy.Address

'aboveR = .Range("A" & 32 & ":" & lastcolumn & lrowrev - 1).Rows.Count ' amount of rows copied

Set rngins = .Range("A32").EntireRow
Debug.Print rngins.EntireRow.Resize(aboveR + 2).Address


        rngins.EntireRow.Resize(aboveR + 2).Insert xlShiftDown 'insert the amount of rows, we copied
        'Range("A" & lrow).Offset(5).EntireRow.Hidden = False

             Set rngins = .Range("A32")
             Debug.Print rngins.Address
             rngtocopy.Copy
             rngins.PasteSpecial Paste:=xlPasteAll

标签： excelvba

Answer 1

现在我不希望按钮被复制。我怎样才能做到这一点？

您也可以使用PasteSpecial。您可以利用XlPasteType 枚举仅复制和粘贴相关部分。例如，如果您想粘贴，这里有一个衬里

一个。除图像外的所有内容

rng.PasteSpecial Paste:=xlPasteAllUsingSourceTheme

乙。只有价值观

rng.PasteSpecial Paste:=xlPasteValues

在行动