Copying a flagged row doesn't work - still!

[MeGustaXL]

Darn It, I've got to bring this one up again I'm afraid

I've used the method that Skip re-'splained to me in thread707-1278315

I've got a range from D5 to J28 which is named "EBS_table".

When I run the macro, it stalls at the Autofiltering part with a "400" error Where am I going wrong?

[green]'....Generate random numbers and fill in another table....
'Then go to the AutoFilter bit:[/green]

[EBS_table].AutoFilter Field:=5, Criteria1:="1"
[D6].CurrentRegion.Copy
    Range("Z65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, _
        Operation:=xlNone, _
        SkipBlanks:=True, _
        Transpose:=False
[EBS_table].AutoFilter

[green]'Carry on and do the rest of the macro....[/green]

It appears to do the Copy, because I can manually select the next blank cell down in column Z and PasteSpecial, Values with (nearly) the desired result. I say nearly, because it copies the header row from D5:J5 - but I don't want that. 

Chris

[b][i]Varium et mutabile semper Excel[/i][/b]

[URL unfurl="true"]www.insys-ltd.co.uk[/URL]

 

[PHV]

You may try to replace this:
[EBS_table].AutoFilter
with this:
Range("EBS_table").AutoFilter

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MeGustaXL]

Hi PH,

Alas that doesn't fix it

I tend to use the square brackets [] to speed things up, having been told (on this site) that:

[Name_of_range].AutoFilter

is synonymous with:

Range("Name_of_range").AutoFilter

but much quicker.

Any other suggestions?



Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[xlbo]

not necessarily quicker - just quicker to type
[ ] is actually an evaluation rather than a syntax thing

as to the question, I would use:

[EBS_table].Copy
rather than
[D6].CurrentRegion.Copy


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MeGustaXL]

My apologies to Geoff and PH, but I've kinda double-posted this and Skip's replying on thread707-1278315 again.



Rather than split that moggy in half before excoriating it, can we close this thread and continue the discussion on the other one?



Chris

Varium et mutabile semper Excel

http://www.insys-ltd.co.uk

 

[xlbo]

no probs - consider this thread closed !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question