PDA

View Full Version : copy and paste only certain rows macro



daveyc3000
06-12-2015, 09:14 AM
im looking to make a macro so that ONLY copies and pastes rows in column B that contain the word "FRN." ....and "FRN" always appears on the RIGHT side of the cell

thanks

mancubus
06-12-2015, 12:41 PM
you shuold be more specific when describing your requirement.

comment/uncomment lines which suit; change and adopt folder and file names...



Sub vbax_52888_copy_rows_based_on_condition()

With Worksheets("Sheet1") 'change sheet name to suit
'filter all values in column B that ends with FRN, if any
.Cells(1).AutoFilter Field:=2, Criteria1:="=*FRN"
With .AutoFilter.Range
'auto filter range contains rows other than header row, ie there are cells in col B that meet the criteria
If .Rows.Count > 1 Then
'option 1: include headers
.Copy
'option 2: exclude headers
'.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
End If
End With
End With

'option 1: copy to another sheet, same workbook
Worksheets("Sheet2").Paste 'change sheet name to suit

'option 2: copy to another (open) workbook
'Workbooks("AnotherOpenWorkbook.xlsm").Worksheets("Sheet2").Paste

'option 3: copy to another (closed) workbook
'Workbooks.Open("C:\Users\MyName\Documents\ms_excel_test_files\AnotherClosedWorkbook.xlsm").Worksheets("Sheet2").Paste

'option 4: copy to new workbook
'Workbooks.Add.Worksheets(1).Paste

'remove auto filter
ThisWorkbook.Worksheets("Sheet1").AutoFilterMode = False

'kill "marching ants" around the copy range
Application.CutCopyMode = False

End Sub