2

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.

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:

enter image description here

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

ChrisC
  • 892
  • 2
  • 12
  • 33

1 Answers1

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.

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:

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

Vityata
  • 42,633
  • 8
  • 55
  • 100