Copy from a filtered sheet in excel vba

Question

I’m having a problem, I need to extract a list of excel data that I Filtreed with over 200,000 lines, i have the option to make excel analyze each line and delete it but I wanted to do this without losing the data already present so I searched and found a way to only copy what is visible (that left active in the filter) but with that when I save the macro what only weighed 10mb ta weighing 16mb and excel is choking, this is not interesting because I need to send by email, if I copy manually the filter and paste it solves but I need agility. Would anyone know what’s wrong with that code?

Sub extrair()

''''''''' limpa a planilha que vai receber a lista ''''''
Sheets("Base").Select 
Cells.Select
Range("A1").Activate
Selection.ClearContents
Range("A1").Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''' insere o filtro CE  na coluna de estados '''''

Sheets("INF").Select

If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1").AutoFilter
End If
ActiveSheet.Range("$A$1:$CB$1048575").AutoFilter Field:=68, Criteria1:="CE"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''    


''''''''''''''''' seleção do filtro o que deve ser copiado ''''''''''''''''''''''
fimr = 1048576
contR = 1
contC = 1

Do While Cells(fimr, 68).Value <> "CE"
fimr = fimr - 1
Loop

contR = 1048576 - fimr

valor = Cells(1, contC).Value

Do While valor <> ""
contC = contC + 1
valor = Cells(1, contC).Value
Loop

Range(Cells(1, 1), Cells(contR, contC)).Select


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''' copia só o que está visível e cola na planilha Base''''''''''''
Selection.SpecialCells(xlCellTypeVisible).Select

Selection.Copy
Sheets("Base").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''' retorna ao local do botão da macro ''''''''''
Application.CutCopyMode = False

Sheets("Macro").Select

End Sub

Answer 1

You can use Find and Select -> Go to Special -> Only visible cells, copy and paste.

Next figure

The code of the Macro looked like this:

 Range("A4:B11").Select
 Selection.SpecialCells(xlCellTypeVisible).Select
 Selection.Copy
 Range("G9").Select
 ActiveSheet.Paste

Answer 2

See if this way is faster with the amount of data you have: (this section should replace the part of your code that copies, use it after applying the filter)

Call Plan1.AutoFilter.Range.Copy
Call Plan2.Paste

Note: I imagined that you are using the direct filter in the datasheet (autofilter).

Answer 3

I’m new to VBA, and I would ask, please, for your help..

I have a spreadsheet Excel with a certain column, and I need to create a macro that does the following:

• Create a connection to the Database, to read a column of a given table, and check if each record of my column Excel is in my Bank column, and if there is:
• Take record to record and filter on Excel one by one, and play each in a new file Excel different.
• After that, e-mail each new file created.

Ex: Spreadsheet "Data", I have the column with the person’s id, I will have to see in my table if this id exists, and if yes, I will bring it in my Excel, create a new file Excel to save this record, and I will email this file to some people. I will have to do this for every record of my Excel leading...

Do you have any idea how I can do this?