copying cells

[andym2712]

Hi,

I've found plenty of information about copying data from one page of a worksheet to another, but my problem is a little more specific.

Assuming we're on Page 1 and using column A and the descending numbers 1-8:-

7
6*
15
12*
4
8
10*
24

Is there a way to transfer only those numbers with the asterix to page 2 of the same worksheet in the same positions (ie rows 2,4,& 7). (It doesn't have to be an asterix)

Cheers

[kellyfspringer]

Here's an idea:
Make a copy of Page 1 (right click on the tab and choose Copy)

Put on an autofilter on column A (Data>Filter>Autofilter)

Then choose a custom filter (the custom filter box gives you a box on the left to choose what search type you are using, and then one on the right to enter your criteria:
[does not contain ] [* ]

While the autofilter is hiding all of the numbers with the asterix, you can highlight and hit the delete button to delete all of the unwanted numbers.

Remove the autofilter, and you should have only the numbers with the asterix left, in the spots that you needed them, and in a separate page than your original data.

[pike]

the basic recorded macro code would be

Please Login or Register  to view this content.

[pike]

or another better option use macro

Please Login or Register  to view this content.

[DonkeyOte]

Pike, I think that should read Columns(1) rather than (0) ?

An alternative approach:

Please Login or Register  to view this content.

[pike]

DonkeyOte
You would think it should be 1 but because I inserted a column 1 ,1 became 0
I have noticed this happening when inserting any columns thats why I always delete inserted columns to set the coulmn numbers right.
I like the use of evaluate and not having to insert any columns

[DonkeyOte]

Sorry, I didn't notice you were qualifying with .UsedRange

Evaluate is a very handy tool for avoiding Iteration, insertion of Formulae etc but pending complexity of the calculation to be performed it can become a little cumbersome to code.

[pike]

Ok .... usedrange .... thats why it probably works so well with arrays ... thanks