Excel-VBA：将可见单元格复制粘贴到可见单元格

I am looking to write a macro to copy the format of only the visible (i.e. non-hidden, non-filtered out) cells of a selection in one sheet into only the visible cells in a selection in another sheet.

我正在寻找一个宏来将一张纸中选择的可见（即非隐藏，未过滤掉）单元格的格式仅复制到另一张纸中选择的可见单元格中。

My VBA is a bit rusty, so if anyone can either help me with the code I wrote myself, or provide an alternative bit of code, I would be very thankful.

我的VBA有点生疏，所以如果有人能帮助我自己写的代码，或提供一些替代代码，我会非常感激。

Here is my current code (with commented-out code incase someone suggests i uncomment that code):

这是我当前的代码（带有注释掉的代码，有人建议我取消注释该代码）：

Sub Copy_Paste_Visible()



    Set CopyRng = Application.Selection
    CopyRng = CopyRng.SpecialCells(xlCellTypeVisible).Copy' _
        'Destination:=Range("A1").Offset(ColumnOffset:=1)



    Set PasteRng = Application.InputBox("Paste to :", xTitleId, Type:=8)

    PasteRng = PasteRng.SpecialCells(xlCellTypeVisible) '.Copy _
         'Destination:=Range("A11").Offset(ColumnOffset:=1)

    'CopyRng.Copy
    PasteRng.Parent.Activate
    'PasteRng.Activate
    PasteRng.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

End Sub

With this code, on line

使用此代码，在线

PasteRng.Parent.Activate

I get the following error message:

我收到以下错误消息：

If anyone is able to provide any input, that would be great.

如果有人能够提供任何输入，那就太棒了。

1 个解决方案

#1

Try this:

尝试这个：

 Public Sub CopyOnlyVisibleCells()

    Dim rangeToCopy     As Range

    Set rangeToCopy = Selection
    rangeToCopy.SpecialCells(xlCellTypeVisible).Copy
    Worksheets(2).Range(rangeToCopy.Address).PasteSpecial xlPasteAll
    Application.CutCopyMode = False

End Sub

Pretty much it takes the selection and it copies the visible cells to the same address of the selection in Worksheets(2). You may play a bit with it to make it work better.

几乎需要选择，它将可见单元格复制到Worksheets（2）中选择的相同地址。您可以使用它来使其更好地工作。

Concerning the error - if you are trying to activate the parent range of PasteRng, then most probably you mean a worksheet. Thus something like this should work:

关于错误 - 如果您尝试激活PasteRng的父范围，那么很可能您的意思是工作表。因此这样的事情应该有效：

Worksheets(PasteRng.Parent.Name).Activate

In general, usage of Select and Activate is considered a bad practice - How to avoid using Select in Excel VBA

通常，使用Select和Activate被认为是一种不好的做法 - 如何避免在Excel VBA中使用Select

#1