Copy rows based on criteria

captainyemen

New Member
Joined
Jul 15, 2008
Messages
5
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

kikiusq

New Member
Joined
Feb 13, 2011
Messages
7
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

New Member
Joined
Jul 15, 2008
Messages
5
Hello,

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

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

Well-known Member
Joined
Oct 20, 2008
Messages
682
Hi,

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

Code:
'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

New Member
Joined
Feb 13, 2011
Messages
7
apply filter on your header row and you can use filter in col A to display data that don't begin with "x".
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,495
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top