VBA to Only Copy Visible Columns, Paste Columns into New Tab, and Dynamically Add Onto Latest Rows With Data

[zakkair]

Hello,

I am trying to modify the code below to copy only visible cells and paste it in the specific destination on another sheet. How would I modify the code below such that if I apply a filter to Sheet1, the data pasted into Sheet2 will be only the visible Cells?

Thanks in advance for all the help.

Sub copycolumns()Dim lastrow As Long, erow As Long


lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 1)


Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 2)


Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 3)
Next i


Application.CutCopyMode = False
Sheet2.Columns.AutoFit
Range("A1").Select


End Sub

 

[veryamusing]

I think this does what you want...

Sub copyVisColsOnly()
    Dim rng As Range: Set rng = Selection
    rng.SpecialCells(xlCellTypeVisible).Copy [Your Destination Here]
End Sub