Problems with nested loop to copy a range

**Edejager** · 04-04-2013, 04:06 AM

Hi

I have a problem with a nested loop. I know there is something wrong in it, but cannot figured out what this is. Can you help me with this?
The code have to look if a cell contains something, if so then that cell + 5 have to be copied from sheet 2 into sheet 11.
After that it have to look at the next cell and do the same thing but then past it next to the previous range.
The problem is within the loop because it works when I am using the range like this: Sheet11.Range("R7:AC7,R22:AC22,R37:AC37.....until... R352: AC352").SpecialCells(xlCellTypeBlanks).Cells(1).Select
But I need a range until R865: AC865. But when I am using that excel cannot handle such a long range.
The same is the problem at the second part of the code

The VBA code is following:

Please Login or Register  to view this content.

Kind regards

**patel45** · 04-04-2013, 04:14 AM

attach a sample file for testing

**Edejager** · 04-04-2013, 05:01 AM

Sorry, here is the file.
It was to big to post it unzipped.

The sub that give the problem is written in module16.

Labflow Lite.zip

Thanks a lot

**patel45** · 04-04-2013, 05:53 AM

very strange formatting on sheet LabFlow Lite Sequencing, the error is due to the SpecialCells method
why the strange formatting ?
why do you use SpecialCells method ?
if you eliminate it the code works

**Edejager** · 04-04-2013, 07:28 AM

Thanks a lot the code works indeed,
but the problem now is that only the last selected cells are copied. And not start at cell "U5" and copy each range to the other sheet.
So first U5:U10 then V5:V10 and so on..
And every time a range is coppied excel have to look if the target cell is empty. therefore I used the specialCells method.

Thanks again