Excel: Find all, select, copy the selection to another sheet question

[azzi2000]

Is there a command in Excel to copy all the rows that it meets a find criteria (similar to find All) and copy/paste them to another sheet.

I have it coded in a loop to find one by one then copy paste... My pseudo code is somthing like this:
(pseudocoded)
Do
sheet(1).select
range("A1").select
find string
Rows(ActiveCell.Row).Select 'Select Entire Row
Selection.Cut
Sheet(2).Select 'Select another sheet
Application.Goto Reference:="R65000C1" 'go to end
Selection.End(xlUp).Select 'go to last row with c
ActiveCell.Offset(1, 0).Select 'Move Down one row
ActiveSheet.Paste 'Paste it
Until not found

I am trying to simply the loop thing.
Please advice,
thank you.
Dré

 

[Simmy]

Hi Azzi
I had a problem much the same and the easiest way round I found was to run through the sheet and delete any rows which don't match your criteria and then just copy the sheet and paste to a new book
Hope this helps!

Simmy

 

[azzi2000]

Simmy:
I am trying to get around the loop row by row.
Thanks anyways,
Dré

 

[DPlank]

You could put an autofilter on the rows, copy the visible rows and paste them to the other sheet.

Back to the first sheet and remove the filter.

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[Gavona]

Have a look at data,filter,advanced filter (or autofilter if simple).

If you are trying to copy values but no formulae then Advanced filter on its own will bo the job, copying the data that meets your criteria to a new location (which can be in a different worksheet of the same workbook. Use named ranges for your database, the criteria range and crucially for the extract range.

If you have formulae then use advanced filter to FilterinPlace and then copy visible cells only (I am not sure this works, you may need to do as Simmy suggested - Filter or sort to display the rows you want to delete...)

Watch out if any cells have more than 255 characters, also if you are concerned to preserve formatting.
Also different approaches will take different degrees of formating with them.

If your data is not so well structured as a database then you could simply insert a new row1. Poplatae this with headings like Field1, Field2.... Hghlight the usedrange and name it "database". Then delete row 1 when you have finished.

Thanks,

Gavin

 

[Gavona]

oops, children came home from school, got distracted, Dave got their first... In my post "Poplatae" means "populate"!

Thanks,

Gavin

 

[azzi2000]

I think this would be the best approach applying filter.
Thank you all for the responses
Tahnk you.
Dré