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.
It opens a .CSV file (user choice) filters it and copies filtered data to .xlsm file.
Code:
[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]
Last edited: