VBA - Copy only visible cells from sheet to another worksheet

Question

I have worksheet ("Formatted Data") and worksheet("Client_1 Data")

I run Macro which do following steps:

select worksheet("Fromatted Data")
autoFilter data in Column "C" with value "client_1"
copy selected columns from worksheet ("Formatted Data") and Paste data to worksheet("Client_1 Data")

What is my issue:

macro copy not only Data i filtered but all of them, veen if they are not visible.

My Macro Code:

Sub PRINT_AVIVA_ISA()

Sheets("Formatted Data").Select
ActiveSheet.Range("$A$1:$R$73").autofilter Field:=3, Criteria1:=Array( _
    "client_1"), Operator:=xlFilterValues

Dim LastRow As Long, erow As Long

LastRow = Worksheets("Formatted Data").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow

Worksheets("Formatted Data").Cells(i, 2).Copy

        erow = Worksheets("Client_1 Data").Cells(Rows.Count, 1).End(xlUp).Row

        Worksheets("Formatted Data").Paste Destination:=Worksheets("Client_1 Data").Cells(erow + 1, 1) ' --- account number

        Worksheets("Formatted Data").Cells(i, 3).Copy

        Worksheets("Formatted Data").Paste Destination:=Worksheets("Client_1 Data").Cells(erow + 1, 2) ' --- designation

        Worksheets("Formatted Data").Cells(i, 4).Copy

        Worksheets("Formatted Data").Paste Destination:=Worksheets("Client_1 Data").Cells(erow + 1, 3) ' --- fund name

        Worksheets("Formatted Data").Cells(i, 5).Copy

        Worksheets("Formatted Data").Paste Destination:=Worksheets("Client_1 Data").Cells(erow + 1, 4) ' --- fund code

        Worksheets("Formatted Data").Cells(i, 7).Copy


    Next i
End Sub

What i need:

put into my existing code something to copy only filtered data?

Thanks,

Peter.

i checked this and added .SpecialCells(xlCellTypeVisible) but its not working .. Im doing something wrong. — Cuchy

basodre basodre · Accepted Answer · 2019-01-11T19:04:52

The problem that you're running into is that you're looping through all of the cells in your 'formatted data' worksheet. The VBA code doesn't check to see if the cells have been filtered or not.

I'm attaching some code below that should do what you're attempting to do. I've made a few changes to clean it up a bit, such as storing sheets into their own variable so that you don't have to recurringly reference them directly.

Also, I opted to use direct value assignment as opposed to copy/paste. Assigning the value directly is usually quicker and has cleaner, more self-descriptive code. The tradeoff is that it doesn't copy over formatting. If you really need formatting, you can add it in once (either at the start or end of the routine, for the entire column).

See if you can adapt the below code and let us know if you need more help.

Sub PRINT_AVIVA_ISA()
    Dim sData As Worksheet
    Dim sClient As Worksheet

    'Prevents the application from rendering graphical elements during processing
    Application.ScreenUpdating = False

    Set sData = Worksheets("Formatted Data")
    Set sClient = Worksheets("Client_1 Data")

    sData.Range("$A$1:$R$73").AutoFilter Field:=3, Criteria1:=Array( _
        "client_1"), Operator:=xlFilterValues

    LastRow = sData.Cells(Rows.Count, 1).End(xlUp).Row

    Dim i As Long

    For i = 2 To LastRow
        If sData.Rows(i).Hidden = False Then
            ' Rather than add 1 to erow 4 times later, just calculate it here
            erow = sClient.Cells(Rows.Count, 1).End(xlUp).Row + 1

            sClient.Cells(erow, 1).Value = sData.Cells(i, 2).Value
            sClient.Cells(erow, 2).Value = sData.Cells(i, 3).Value
            sClient.Cells(erow, 3).Value = sData.Cells(i, 1).Value
        End If
    Next i

    Application.ScreenUpdating = True

End Sub

VBA - Copy only visible cells from sheet to another worksheet

1 Answers