Copy Data to new worksheet without range limits

[bselinger]

Trying to copy specific data from one worksheet to another.
If the text of North America (column A ) is found I want to copy all data with North America to worksheet 2.
The row range will be ever changing as data is added and deleted from the source file.

I looked at advanced filter but it seems to have limitations around ranges.

Would I'd be better off with ifcount ??

Thank you in advance for your quick responses.

Bob

[JBeaucaire]

No, the AutoFilter is the tool to use. Use the macro recorder to get a base recording of:

1) Turn on the AUTOFILTER in the title row above the data
2) Filter column A for North America
3) Copy all visible rows to the other sheet

Now edit the resulting macro so the copy command actually uses a much longer set of rows than you recorded, more than you think you'll ever need. That should do it.

[bselinger]

Thanks Jerry for the quick response... "sometimes we overlook what's in front of us"

[JBeaucaire]

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.