Make a copy of a filtered worksheet in excel vba

Navigation
1

I'm having a problem, I need to extract a data listing from excel that I've filtered with over 200 thousand lines, I have the option to do the excel parse every line and delete it but I wanted to do this without losing the Given already present then I researched and found a way to only copy what is visible (which left active in the filter) but with this when I save the macro that only weighed 10mb ta weighing 16mb and excel is choking, this is not interesting because I need to send it by email, if I manually copy the filter and paste it resolves but I need agility. Would anyone know what's wrong with this 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

    
asked by anonymous 13.03.2015 / 13:49

3 answers

1

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

The following figure

TheMacrocodelookslikethis:

Range("A4:B11").Select
 Selection.SpecialCells(xlCellTypeVisible).Select
 Selection.Copy
 Range("G9").Select
 ActiveSheet.Paste
    
28.05.2015 / 21:16
1

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

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

Note: I figured that you are using the direct filter on the worksheet with data (autofilter).

    
13.03.2015 / 14:00
0

I'm new to VBA , and I'd like to ask for your help ..

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

  • Create a database connection to read a column of a given table, and check that each record in my Excel column is in my Bank column, and if it exists:
  • Take record to log and filter on Excel one by one, and play each one in a new Excel different file.
  • After that, email each new created file.

Ex: "Data" worksheet, I have the column with the id of the person, I will have to see in my database table if that id exists, and if so, I will filter it in my Excel , I will 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%% of main% ...

Do you have any idea how I can do this?

    
30.11.2016 / 19:01
IoC with Ninject Directory index forbidden