Need Macro to Create New Worksheet Named After Currently Selected Cell

[logabr01]

Hello all,

I am new to the forum and entirely new to VBA. I have been working on a project and have managed to write a couple macros thus far, one that returns the current sheet name to a cell, and two that protect or unprotect all sheets in the workbook.

I am stuck on this macro now, and have been looking everywhere with no avail. What I need is just a simple macro to take the currently selected cell and create a new worksheet with that name (it is a string). I also need to paste a template into that new worksheet from a pre-existing sheet.

Any help would be greatly appreciated, this is my first post. I am using Excel 2010 by the way.

-Brett

[stnkynts]

This should help you with part1. A lot more information is needed to assist you with your second question.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Maybe something like

Please Login or Register  to view this content.

and to

[mike7952]

See if this will get you started. The sub calls function GoodSheetName to validate a good sheet name

Please Login or Register  to view this content.

[logabr01]

@stnky

That macro worked great.

As for the second part, I have a template sheet that I want copied into the new sheet after it is made. It could be either the entire contents of the sheet, or just the values and format of columns A through N. Does that help?

-Brett

[logabr01]

@mike

Your macro gets me the copy of Template, called Template (2), and a Run-time error '424': Object Required. When I run debug, I get .Name = shName highlighted.

[mike7952]

Try this then

Please Login or Register  to view this content.

[logabr01]

@mike

Nailed it, Thanks!