How to stop #Ref! errors with macro that cuts and pastes from list

[The Bantam]

Hi all,

Have used Excel for work for years but am only recently starting to realise and learn what VBA can do. A nice suprise!

I am currently working on a large list of items which basically consists of the item description, the business division it relates to (often more than one item per division), and the sales that relate to it.

The list has an autofilter on and a total row.

I have written a marco that allows me to to run a report for which ever divisions I want to select, that selects that division on the autofilter, and cuts and pastes various columns into a new sheets that I can send out.

However, whilst it works very well, if there is a nil return - for example Division D does not have any items this month - rather than just cutting and pasting the zero value that is in the list, I get a "#Ref!", meaning I then have to go through all the divisional sheets hard typing a zero over the error.

Any tips on how to stop this happening?

Thank you in advance!

Rich

[shg]

Maybe test before applying the autofilter?

[The Bantam]

But without the autofilter, it can't select data by a particular division and thus cause the problem!