I'm created a workbook that opens other files, copies data, filters it,
then deletes all the unwanted lines. The problem I have is that it's 80
worksheets in this one file and I the macro goes through 80 separate
files, one at a time, and then closes it down after it is done copying
the lines. Having so much data, Excel craps out and tells me that there
aren't enough resources to finish the action. I believe this happens
somewhere halfway through the sheets at the line:
Selection.AutoFill Destination:=Range("F7:F4000")
So my question is whether or not there is a better way to write the
below that will filter out for the lines that I want:
ActiveCell.FormulaR1C1 = "=RC[-5]=R1C2"
without having to fill 40,000 cells with formulas over 80 separate
worksheets. Thanks so much in advance for any help possible!!


Sub Import_Files()
If Range("B4").Value = """" Then
Exit Sub
End If
Dim WB As Workbook
Set WB = Workbooks.Open(Filename:="N:\test\" & Range("B4").Value)
Range("A1:E40000").Select
Selection.Copy
For Each myworkbook In Application.Workbooks
If myworkbook.FullName = ThisWorkbook.FullName Then
myworkbook.Activate
Next myworkbook
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
WB.Close Savechanges:=False
Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-5]=R1C2"
Range("F7").Select
Selection.AutoFill Destination:=Range("F7:F4000")
Range("F7:F40000").Select
Range("A6:F6").Select
Range("F6").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:="FALSE"
Rows("7:40000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=6, Criteria1:="TRUE"
Columns("F:F").Select
Selection.AutoFilter Field:=6
Selection.Delete Shift:=xlToLeft
Selection.AutoFilter
Range("B6:B40000").Select
With Selection.HorizontalAlignment = xlRight
End With
Range("A6").Select

End Sub