Extracting data based on cell value

In the bottom of the 'AU' column on the 'Printers' tab of the enclosed workbook, I end up with a bunch of additional rows called 'New row'. This can vary from none to hundreds depending of various criteria.

Up to now i have been able to cut and paste these rows + the header row, to another worksheet in the same workbook called 'New Rows' using the following code;

Please Login or Register  to view this content.

I should point out that the example provided here is an extract from my main workbook that i'm having the problem with. This workbook has 391 rows of data and 338-391 has 'New row' in the AU column

I'm trying to get as much of my daily mundane tasks as automated as possible so any help would be greatly appreciated.

Johnny,
What is the issue? The code is working.

The attached error is not related to the code. You are trying to use the selection method in multi-range, hence the error.

Hi Ab, I have 4 other workbooks (Desktops, Monitors, Notebooks and Scanners) that uses the identical code and it works perfect and it was working with the Printers workbook up until today. The only difference is there are hundreds more rows. See screenshot

the piece of code you provided runs along with 2 others and was working perfectly. What has changed and do you know what i can do to fix this

The code should work with any length of column AU. If there are not values, the code will use this line If LR > 2 Then
to skip the error. I would like to see the error you are getting on attached.

I attached a screenshot image to the thread starter, are you able to see it AB

Johnny,
Yes, I saw the error, but I am unable to locate it to the code. In which line of the code the error occurs?
The yellow line?

AB, found the problem, for some reason some of the columns were hidden, after opening all the columns it works perfect again. Sorry for wasting your time, another lesson learned.

On another issue Ab, i also have empty (blank) columns range: AM to AS. is it possible to add to your code so that a 0 (zero) will be placed in the AI column if this range is blank.

Is it if any cell in column AM-AS is blank add 0 to column AI?

exactly AB

Forget to ask you: You have values of 4 in column AI. What would happen to these values, will they be replaced by 0?

yes, if any of the cells in the AM -AS range is blank

Thats perfect AB, truly appreciate your time and effort. Thank you