MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying certain cells to another worksheet


Posted by Rob on August 08, 2001 2:18 PM

I was wondering if anyone has a macro that will copy a row from one worksheet, to another blank worksheet IF a certain condition is met. Say for example, if on my first worksheet, i have 10 columns of data, and 100 rows, and i want to have all the rows with an "X" in the first column to be copied to the blank worksheet.

I KNOW its possible, im just clueless as to how to do it :) thanks


Posted by Barrie Davidson on August 08, 2001 2:54 PM

Rob, does your data have headers in the first row?

Barrie

Posted by Rob on August 08, 2001 3:29 PM

Yes, it does have headers

Posted by Barrie Davidson on August 08, 2001 5:30 PM

Re: Yes, it does have headers

Okay Rob, I've written a macro that will do what you are describing. You'll have to make a few changes (data range, filter criteria), but I think this will do the trick.

Sub Extract_Data()
'Macro written by Barrie Davidson

'Variables used by the macro
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Select the first 10 columns and first 100 rows
Range("A1:J100").Select
'Apply Autofilter
Selection.AutoFilter
'Get the filter's criteria from the user
FilterCriteria = InputBox("Enter condition to be met")
'Filter the data based on the user's input
'NOTE - this filter is on column A (field:=1), 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
'Open a new file
Workbooks.Add Template:="Workbook"
'Get this file's name
NewFileName = ActiveWorkbook.Name
'Make sure you are in cell A1
Range("A1").Select
'Paste the copied cells
ActiveSheet.Paste
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
Workbooks(CurrentFileName).Activate
'Clear the autofilter
Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
Range("A1").Select

End Sub

Regards,
Barrie

Posted by Rob on August 09, 2001 11:32 AM

Thanks Barrie!