Microsoft Office Excel Error while Copying Data

[vma2084]

Hi All,

I am facing an error while copy data from 3 files into 1 file.


-- removed inline image ---


{
Microsoft Office Excel Cannot Create or Use the Data Range reference because its too complex. Try one or more of the following:

-Use data that can be selected in one contiguous rectangle.
-Use data from the same sheet

[OK]
}

To explain the problem I have attached a sample of the 3 files which I have been working on in the below link. I have to filter for a certain condition mentioned below, post which the resultant should be copied into a new file into 3 respective tabs and also combine all of them into the 4th tab.

Now when I copy them into the respective tab the Second file copies with no problem file, while the 1st and the 3rd file faces problems by popping up the above error and the whole data gets copied

Files Names with the Filter Condition in Bracket
File 1.CSV (DeptID = 12) - contains 19725 Rows
File 2.CSV (DeptID = 30) - contains 9096 Rows
File 3.CSV (DeptID = 12) - contains 84513 Rows

Files are located in the below link :wink:
http://www.esnips.com/web/vma2084ExcelFiles/

Could you please help me on the above, I am think if there is a way to copy the filtered data part by part.

Thanks alot in advance

Regards,
VMA

 

[AlphaFrog]

There is a limit to the number of non-contiguous ranges (cells that don't touch each other) that Excel can copy in one step. I don't know what that number is offhand, but apparently you have surpassed that limit in two of your files.

One way around that limit is to sort your data 1st. If all the rows you filtered on were contiguous, you could then copy them.

• Select all the data columns
• Select from the menu Data\ Sort
• Sort on the Dept ID column
• Filter on the Dept ID column
• Copy-Paste the filtered data

 

[vma2084]

Thanks alot Aplha Frog, i think it worked.

However I was thinking, is there a way to copy the data part by part through a Macro