Modification of macro to clear not delete page.

**jad70** · 02-26-2009, 12:03 PM

Hi All,

I have a macro that I found on the net for copying a filtered selection and copying to a new sheet. Works great and thanks very much to the donor. I would like to alter it slightly so that it just clears the sheet called 'Interval tasks' instead of deleting it and creating a new one. Here is the code:

Please Login or Register  to view this content.

End Sub

I have had a couple fo attempts to do it but so far no luck. Can anyone help?

Many thanks,
JD

**royUK** · 02-26-2009, 12:09 PM

Try this

Please Login or Register  to view this content.

**jad70** · 02-26-2009, 12:17 PM

Roy,

Many thanks for the very quick reply.
I am getting a compile error at this line

Please Login or Register  to view this content.

Any suggestions?

JD

**royUK** · 02-26-2009, 01:10 PM

Should be

Please Login or Register  to view this content.

**jad70** · 02-27-2009, 06:46 AM

Hi Roy,

I just found the exact same thing on another thread in this forum. You replied to this also, so I have copied in that code.
It now stops here with error 91 and inserts the formatting on the Info sheet.

Please Login or Register  to view this content.

I have attached the workbook. I want to insert the copied range at A2 as I have some script to put in A1. This is done with the 'format' macro which I will have to re-write. Basically only want the range that starts in A2 to be deleted on the sheet 'Interval Tasks'

Many thanks,

JD

**jad70** · 02-27-2009, 07:49 AM

HI Roy,

So, managed to track down the cause- WSNew was never set. (I think)
But now it stops at another line of .Select

Please Login or Register  to view this content.

A bit confised as this all worked before.

Thanks again.

JD

**royUK** · 02-27-2009, 08:06 AM

can you attach the workbook?

**jad70** · 02-27-2009, 08:20 AM

Certainly Roy.

The macro is in module2. No matter what I do it still stops there. It was stopping in the sort selection that I have now moved to the code-sortfilter- but now it just stops at the .Select.
If it all works the filtered range should be copied at A2 and there will be some script in row 1 (of sheet Interval tasks). I will alter this as it was written due to the sheet being re-generated.

Many thanks.

JD.

**jad70** · 02-27-2009, 05:11 PM

Hi All,

So, it would appear I have solved this using 'Advanced filter', copying the filtered data to another sheet, and it is so much easier.
Here is the code:

Please Login or Register  to view this content.

I hope this helps more people out as there seems to be a lot of people who are trying to do the same thing.
I have also attached the workbook so you can see that it does indeed work.

I have seen though some issues with Excel 2007 and this macro might not work. I am thinking of the future users of this database and would not like to fall into a big trap. Has anyone got experience of this?

Anyway, feeling quite good to be able to contribute something at last.

JD