Help to improve performance of code VBA

[Lenna]

My code (below) executes but it gives me a message that there is too much data on the clipboard, even when I have only few rows. User has to click "OK" to move on... it's very annoying. Please suggest how to improve this code in other aspects as well.

It opens a .CSV file (user choice) filters it and copies filtered data to .xlsm file.

[INDENT]Private Sub CommandButton1_Click()



Dim csvFile As Variant
Dim csvBook As Workbook


csvFile = Application.GetOpenFilename("Text Files (*.csv),(*.csv),,Please select CSV file to open")
If (csvFile <> False) Then
Workbooks.Open csvFile
Set csvBook = ActiveWorkbook
End If
'Trim column O
Columns("O:O").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
    Range("O1").Select
    Selection.Copy
    Range("A1").Select
    Selection.End(xlDown).Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("O" & FinalRow).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("P:P").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select


'filter data
Cells.Select
    Selection.AutoFilter
    
        FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
'ActiveSheet.Range("$A$1:$O$" & FinalRow).AutoFilter Field:=13, Criteria1:=Array( _


    
    ActiveSheet.Range("$A$1:$Q$" & FinalRow).AutoFilter field:=10, Criteria1:=Array( _
        "AT1R", "C1Q_I", "C1Q_I_RPT", "C1Q_II", "C1Q_II_RP", _
        "FL__PRAI", "FL__PRAI_RPT", "FL__PRAII", "FL__PRAII_RPT", "FL_EPC_XM_ALLO", _
        "FL_XM_ALLO", "FL_XM_ALLO_RPT", "FL_XM_AUTO", "GP__I", "GP__IDv2", "GP__II", _
        "GP__MICA", "GP_MICARPT", "IBEAD_SABI", "LCTI", "LPRI", "LPRI_RPT", "LPRII", _
        "LPRII_RPT", "LSM__MICA", "LSMI", "LSMI_RPT", "LSMII", "LSMII_RPT", "LSMMICA_RPT", _
        "MICA_SAB", "SABI", "SABI_Dilution", "SABI_IgM", "SABI_RPT", "SABII", _
        "SABII_Dilution", "SABII_IgM", "SABII_RPT", "Serum_Stored"), Operator:= _
        xlFilterValues
   
Columns("A:Q").Select
Selection.Copy
Windows("SerologyQAMacro.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
csvBook.Close SaveChanges:=False ' or True


End Sub[/INDENT]

 

[Lenna]

I got it to work!

Thanks,

Lenna

 

[Peter Thompson]

Peter,

I've tried your code and it places trim(rc[1]) in O1.

What am I missing?

Lenna,
It was a typo on my part there should have been an = before trim.

 

[pdevito3]

Not sure how long it's taking you but another way to significantly decrease your run time is to add the following,

Application.ScreenUpdating = False

' your code here

Application.ScreenUpdating = True