Copy rows based on criteria

[captainyemen]

Hello,

I've been looking for something quite simple, but cannot seem to get my head around this.

I would like to copy a row of data from one worksheet to another, based on there being an 'x' in the first column, i.e.

Column = A, B, C, D, E

Row 1 = _, NL , Shop, £3m, 5Yr
Row 2 = X, DE, CRM, £5m, 2.5Yr

i.e. I would like to have only row 2 copied across to a new worksheet, in a specific area.

I have around 20 columns of data to copy across, so would like to keep that element fixed.

I am hoping this makes sense - could anyone provide me with some steer? Would prefer a macro, as I can't seem to make a vlookup/match function work

Many thanks
CY.

 

[kikiusq]

you can sort by col A to list all the rows with x together, copy them over, and then undo the sort in the original sheet.

 

[captainyemen]

Hello,

Thanks for this - I should have been clearer in the first place

Copying over will not be a one-off exercise, but is based on a prioritisation on different combinations, therefore I would prefer not to go through the exercise again and again, without amending the main datasheet where the data sits.

If there is a way on displaying just the rows with an 'x' on a different sheet, that would be great.

 

[crook_101]

Hi,

The code below will get you started - it is commented so just make the changes as highlighted.... Hope this helps

'this will put your data in a new worksheet
'it will also Auto fits text in Columns on the new sheet
Sub Extract_Data_Two()
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentsheetName As String
Dim NewFileName As String
'Get the current sheets's name
CurrentsheetName = ActiveSheet.Name
'Select the range
'(note you can change this to meet your requirements)
Range("A1:K63000").Select
'Apply Autofilter
Selection.AutoFilter
'Get the filter's criteria from the user
FilterCriteria = "C"
'Filter the data based on the user's input
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=1, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy
Sheets.Add
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
' Auto fits text in Columns
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
'Go back to the original sheet
Worksheets(CurrentsheetName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Cheers,
Ian

 

[kikiusq]

apply filter on your header row and you can use filter in col A to display data that don't begin with "x".