VBA Template with Macro

[pczarnota]

Hello,

I am creating a template for our ops team which I want to include a macro that will name the sheet based on a cell within the sheet. I have found how to name the cell but have had a hard time figuring out how to create a button that will create a new sheet within the workbook that contains the macros.

The excel doc contains static text in A1, A2 and A3. Users will fill out B1, B2 and B3 and the sheet will be renamed using the contents of cell B1.

I tried using a copy macro but that causes issues because it copies everything the user entered and because of that, it throws an error because two sheets cannot have the same name.

The macros which I am using to rename the cells is entered by right clicking on a sheet > View Code and pasting the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)Set Target = Range("B1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

The copy code, in case anyone is wondering, is:

Sub CopySheet_Beginning1()
ActiveSheet.Copy Before:=Worksheets(1)
End Sub

Thank you!

 

[Dim Me as xlNoob]

You can try changing the sheet's selection change event to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    If Intersect(Target, Range("B1")) Then _
    Application.ActiveSheet.Name = VBA.Left(Target, 31)
End Sub

And with the sub routine to create a new sheet, while in the Visual Basic Editor select Insert > Module from the menus to create a new module then place the sub in there:

Sub CopySheet_Beginning1()
    ActiveSheet.Copy Before:=Worksheets(1)
    Range("B1").Select
End Sub

Now when you copy the sheet it will skip the rename error and then activate the cell ready for user input to name the sheet.

 

[pczarnota]

Thank you, Dim.

I noticed that the name of the sheet doesn't change to what I have entered in B1 and instead will change the sheet's name to whatever data is entered into a cell I click. For example, in A1 I have "Name" typed into the cell. If I click (with my mouse, I am unable to use arrows) cell A1, the name of the sheet changes to "Name". If I click the cell B2, the name of the sheet changes to whatever data is in B2.

The first macro I pasted when into the console after I right clicked on the sheet's tab and selected View Code. Then from there I went Insert > Module. Did I do this correctly?

 

[Dim Me as xlNoob]

No you followed the instructions correctly, I just did some poor coding I mustn't have checked it properly at all! Replace the sheet's selection change code with this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("B1")) Is Nothing Then _
    Application.ActiveSheet.Name = VBA.Left(Target, 31)
End Sub

Now that should fix it up so that the sheet will be renamed with B1 whenever it is edited. Sorry for that.

 

[pczarnota]

No need to be sorry. This is very helpful and I appreciate your time! Thank you.

 

[pczarnota]

I noticed I am back to having the data that was entered by the user (Column B) is also being copied over. Clicking the macro button to create a new sheet should only create a new sheet with Column A data, the macro to rename the sheet based on B1 and the button which I am creating that the user can click to create the new sheet. Sorry for the headache!

 

[pczarnota]

Hey folks, does anyone have any recommendations on this little issue of mine?