Dynamic range for a copy macro.

**jad70** · 02-20-2009, 06:01 AM

Hi all,

I need a bit of assistance in identifying the dynamic range so that my macro below will work:

Please Login or Register  to view this content.

Range A1:T999 above is the result of this filter macro and is dynamic depending of a value in U1(Field 21):

Please Login or Register  to view this content.

How can I make the range to copy dynamic? I have tried several methods but so far no luck.

For those interested I would use the advance filter to copy to another sheet but for the moment it is just moving the whole sheet and not filtering for "TRUE".

Anyway, any help would be gratefully recieved.

JD

**jad70** · 02-20-2009, 11:01 AM

A quick update.

I have created this;

Please Login or Register  to view this content.

which works for the first time, but when the value of Field 21 changes (linked to a listbox in a UF) the copied region does not reflect what is in field 21. i.e. when I recorded the macro the value was 18, but if I change the value to 144 and then run the macro it still only copies the filtered value "true" of 18.

Is there an easier way to do this?

thanks in advance.

JD

**jad70** · 02-22-2009, 08:42 AM

OK, I found this on this site posted by davsexcel, many thanks, and modified it to copy insted of cut:-

Please Login or Register  to view this content.

Unfortunately I get a 'run time error '91': Object variable or with block variabler not set' and when I de-bug the line in Sub CopyFilter

Please Login or Register  to view this content.

is highlighted. Any suggestions anyone????????? BTW, the Sub Filter does work, so there is a range.

many thanks.

JD

**jad70** · 02-22-2009, 04:00 PM

seek and ye shall find...

very good link here to filtering/copying to new sheets etc and very well written to be understood by biffs like me.

http://www.rondebruin.nl/copy5.htm#AutoFilter

onwards and upwards........