Copy columns of visible rows only

[rammi125]

I have a spreadsheet which each day will have a lot of hidden rows (different rows each day). I want to be able to copy the first ten rows of the visible data only for 3 columns (Column A,S,V) and paste it to another spreadsheet, when I currently try to do this with the code below the paste copies the whole row. How do I change it to copy only the three columns that I need? I will need to run this procedure on four different tabs.

Sub test()

Dim lngLastRow As Long
Dim x As Long
Dim rngMyRange As Range


lngLastRow = Range("A1:a40" & Rows.Count).End(xlUp).Row
x = 4 ' change to row you want to start with


On Error Resume Next
Do Until rngMyRange.Cells.Count = 10 Or x = lngLastRow + 1
On Error GoTo 0
If Rows(x).Hidden = False Then
If rngMyRange Is Nothing Then
Set rngMyRange = Range("A" & x)
Else
Set rngMyRange = Union(rngMyRange, Range("A" & x))
End If
End If
x = x + 1
Loop


rngMyRange.EntireRow.Copy Sheets(2).Range("A1")


End Sub

 

[Rijnsent]

Hi rammi125,
could you post your code next time indented within [ code ] brackets, that would make it much easier to read? The copy-pasting of the entire row happens in the last line of your code. If you only want to copy those 3 columns, you could e.g. do this:

rngMyRange.Copy Sheets(2).Range("A1")
rngMyRange.Offset(0, 18).Copy Sheets(2).Range("B1")
rngMyRange.Offset(0, 21).Copy Sheets(2).Range("C1")

Cheers,
Koen