# VBA Copy Visible Cells, Paste Visible Cells into the next empty row, and "Clear Contents of what was copied



## bored622 (Dec 15, 2022)

Hello everyone, 

I'm trying to create a vba button that copies only visible cells from cell range A7 to V7 and rows below. Next, I want to paste that data onto another sheet starting from A7 and only paste it onto the next blank cell. Finally, "clear contents" of what was copied from the first sheet. When I try my macro, it bugs or pastes it weirdly. I'm new to macros, but this is what I got so far. 

Sub test()

    Range("A7:V7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("TheWave").Cells(LastRow, 1).PasteSpecial xlPasteValues

End Sub


----------



## HaHoBe (Dec 15, 2022)

Hi bored622,

maybe


```
Public Sub MrE_1224708_1615D0B()
' https://www.mrexcel.com/board/threads/vba-copy-visible-cells-paste-visible-cells-into-the-next-empty-row-and-clear-contents-of-what-was-copied.1224708/

'///working on ActiveSheet
With Range("A7:V" & Cells(Rows.Count, "A").End(xlUp).Row)
  If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 0 Then
    .SpecialCells(xlCellTypeVisible).Copy
    Sheets("TheWave").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    .SpecialCells(xlCellTypeVisible).ClearContents
  End If
End With
End Sub
```

Ciao,
Holger


----------



## bored622 (Dec 15, 2022)

HaHoBe said:


> Hi bored622,
> 
> maybe
> 
> ...


It worked perfectly! thank you


----------



## HaHoBe (Dec 15, 2022)

Hi bored622,

glad to help on this one - thanks for the feedback.

Holger


----------

