Hi,

I have a worksheet called "data" with 7 groups consisting of 3 records per group (ranked from 1 thrue 3). the 3 records are shown by means of the filter (<=3). FYI Total number of records are around 500.

Each week I do a download which will bring differences in the data, for example record 1 in group 1 (which is filtered) is infact record 300 from the dataset and next week it is record 350. My problem is: the filtered dataset is always 21 (7 groups x 3 ranks) and want to copy and paste them in another worksheet. The problem is when I do a new download, the references in the pastelink is based on the previous week hence data error. sometimes a group of 2 records and the second group holds 4 records.

Is there a way in VB for example to select only the data 21 records and paste them in another location. I tried to use CTRL-A but in the code it states a cell number which is then hardcoded (what I dont want)next week dataset can be larger or smaller (500 records).

Paul,

You want to use the SpecialCells method of the range objcet. Example as follows:

The SpecialCells method replicates the functionality of the GoTo -> Special... box and is very useful for quick filtering to certain types of things (i.e. cells with comments in them, visible cells, etc.)

HTH,
Maccas

Hi Maccas,

Wouldnt give this a problem because G500 is a static value in the code, the list what im talking about is shrinking and growing depending of the amount of data per week, I was looking for a dynamic solution.

rgrds,

Paul.

Well then use:

Great stuff!!! how do you know when to use for example (xlCellTypevisible) ? it does the job.

Thanks again!!!