Creating a copy of a sheet and naming it

[brian.jermain]

Being a newbie to VBA could I please have some advice .

What I am trying to do is write a macro to select the active sheet and
create a copy of it but then I would like an input message to ask me
what to name the sheet so that I am presented with an exact copy
(including macro buttons) but with a name of my choice. I have tried a
myriad of combinations but do not seem able to get it to work

This macro must be able to work with any selected sheet in the workbook

Any help would be appreciated

Brian
Scotland

 

[Earl Kiosterud]

Brian,

SheetName = InputBox("Enter desired name for copy of active sheet")
activesheet.Copy After:=ActiveSheet
ActiveSheet.Name = SheetName

It will copy the selected sheet and put it sheet after the selected sheet.
It won't check that the name entered already exists in a sheet, which will
cause an error.

As a starter, you can start the macro recorder (Tools - Macro - Record new
macro), do the biz, stop the recorder, then modify the code as needed.

 

[brian.jermain]

Earl

Thank you very much - you make it look so easy

Much appreciated

Brian