Using VBA to copy and name a worksheet

Perhaps like this?

Please Login or Register  to view this content.

Hmmm - well, it seems to like it more, but it's now giving me the following error:

Run time error '1004':

Select method of Range class failed

And now I have a new error!

Please Login or Register  to view this content.

... is causing the problems:

Run-time error '1004':
Application-defined or object-defined error

However, once the debugger has run and been closed, it seems to (almost) work with the worksheet. I say 'almost' because it makes one too many copies of the worksheet '1', with the final one getting called '1 (2)'...

Any more ideas??

Thank you!!

Hi rwgrietveld,

Thanks for the advice, but, to be honest, I have no idea what any of what you said means! Part of the problem is that I have had no VB training or anything and am trying to piece something together...

Any chance you can explain/pose your questions in layman's terms...?

Thanks.

fd.

Right - I've looked a bit more at the code, trying to figure what the jiggery is going on and I reckon that this line is the culprit:

Please Login or Register  to view this content.

Not because it's a bad bit of code, but because the section of the range from U3 downwards contains a formula - =if(A3="", "", U2+1) - so the code keeps looking down the cells because there is a formula there, even if the formula prints "" into the cell.

If you see what I mean.

So, is there a way to get the .End(xldown) section of the code only look for the cells where my excel formula has left some actual text, rather than any cell where there's a formula??

Thanks again... fd.

Thanks to everyone for their help on this but I have now found a different way around the problem I had. Or rather a colleague found a different way around it.

I appreciate the time and effort though, thanks again...

fd.