copying worksheet to same workbook and renaming it

[georgemathews]

hi...
i would like to use a macro which copies a worksheet from a workbook to the same workbook and paste it at the end and then renaming it... everything using a macro...
appreciate the help...

[Cheeky Charlie]

Please Login or Register  to view this content.

HTH

NB, will crash if "Brian" already exists

Please Login or Register  to view this content.

is more robust - will simply add (x) after sheet name if duplicated

[davegugg]

Go with:

Please Login or Register  to view this content.

[Cheeky Charlie]

Go with:

Please Login or Register  to view this content.

Don't. There is no need to activate the sheets.

CC

[VBA Noob]

georgemathews or is it macro_user,


Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

VBA Noob

[davegugg]

I see that when you create the copy, vba automatically activates the copy sheet, but you have to have the sheet you are intending to copy already activated, right? So our code is essentially the same (except my Sheets(y).Activate), right Cheecky?

[Cheeky Charlie]

Well, no, there's no need to activate the sheet to copy it, perhaps you could try it:

Please Login or Register  to view this content.

with a different sheet activated - works just the same

The OP asked for the sheet to be placed at the end (so my code: (a) finds the number of sheets and (b) copies the sheet after the last one)

There's no need to activate the copy to rename it

Functionally similar, but these details make a big difference in bigger projects - and piecing together these little bits is how most people on the forum will be learning

CC

[davegugg]

I see what you mean about copying a sheet other than the active one. I am curious though - when you copy a sheet to the end, it seems that copy is automatically activated. Is that true? If so, does having a redundant Sheets("Copy").Activate make any difference? Does it slow down the code?

Thanks, I'm trying to learn just like everyone else!

[Cheeky Charlie]

Yes, you're absolutely right, it is automatically activated.

Yes, right again, it does slow the code - like I said it's not a big individual difference but it's the kind of thing which bloats a bigger project.

Also, using things like "sheet.activate" encourages "lazy programming", like referring to ranges without addressing them to sheet level. So, if you make a macro to exist and run on a single sheet, it's probably fine just to address range(etc.etc.) but as soon as you start copying/moving/deleting sheets with your VBA the references which simply refer to range(etc) will become unreliable where sheets(sheet).range(etc.) will continue to work. Make sense?

All these points are much more relevant to larger projects, but like I've said, it's by assembling the little bits that we make bigger projects so the building blocks should be as good as possible.

And in terms of knowledge - in all fairness we're about a step away from each other on the learning curve with this.

[davegugg]

Thanks, that makes sense