Automatically give new sheet name (created frm template w/macro) the value of a cell

[eivindv]

Hi everyone,

(I am new to VBA and know nothing about it, except that I know how to record a macro.)

I have made an empty template in excel 2010 where I want people to fill in information. When the template is filled out, I want them to press a button that creates a new sheet with the information they filled in (a copy of the template with information) and at the same time gives this new sheet the name of cell S10. I have made the macro for some of it (I recorded what I want to happen), but I dont know how to include in the code that the new sheet that is created gets the name of cell S10. Can you help me with this?

I have tried to put this code inside my macro, but that doesnt work (another question, How do i place a macro within a macro..?)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if range("a1").value<>"" then
Sheets(1).Name = Range("a1").Value
end if
End Sub

My macro/recording looks like this now:

Sub KompMedarb5()
'
' KompMedarb5 Makro
' lag nytt ark, gå tilbake til mal og slett alt, gå til startside
'
'
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Range("Q3:AE3").Select
    Sheets("Kompetansekort").Select
    Range("Q3:AE3").Select
    Application.CutCopyMode = False
    Range("S6").Select
    Selection.ClearContents
    Range("S8").Select
    Selection.ClearContents
    Range("S10").Select
    Selection.ClearContents
    Range("R16:V22").Select
    Selection.ClearContents
    Range("S26:AD30").Select
    Selection.ClearContents
    Range("S34:AD36").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-3
    Range("Q3:AE3").Select
    Sheets("Startside").Select
    Range("A1").Select

 
   
    
End Sub

Where can I put in that I want the new sheets that are made to have the name of cell S10?


Thank you,


Eivind

[Bob Phillips]

Sub KompMedarb5()
 '
 ' KompMedarb5 Makro
 ' lag nytt ark, gå tilbake til mal og slett alt, gå til startside
 '
 '
Dim ws As Worksheet

    With ActiveSheet
    
        Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        .Cells.Copy
        ws.Paste
        ws.Name = .Range("S10").Value
    End With
    
    With Sheets("Kompetansekort")

        .Range("S6,S8,S10,R16:V22,S26:AD30,S34:AD36").ClearContents
    End With

    Worksheets("Startside").Select
    Range("A1").Select
End Sub

[eivindv]

Thank you so much for your quick respons! I removed the recorded macro and replaced it with yours

Excellent, thank you!

Eivind

[eivindv]

Hi again,

In my template I have to have a box/button where I active the macro. This button is also copied in the new worksheets that are added. How can I "hide" or make this macro-button not to be copied in the new sheets that are made? Is this possible?

Eivind

[Bob Phillips]

On the copied sheet, add something like this after you rename the sheet

        ws.Buttons("Button 1").Delete

[eivindv]

I get a run-time error.. let's say I call the button "Button 1" like you said. In the code, I have added the line you wrote like this after renaming it:

Sub KompMedarb5()
     '
     ' KompMedarb5 Makro
     ' lag nytt ark, gå tilbake til mal og slett alt, gå til startside
     '
     '
    Dim ws As Worksheet
     
    With ActiveSheet
         
        Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count - 1))
        .Cells.Copy
        ws.Paste
        ws.Name = .Range("S10").Value
        ws.Buttons("Button 1").Delete
    End With
     
    With Sheets("Kompetansekort")
         
        .Range("S6,S8,S10,R16:V22,S26:AD30,S34:AD36").ClearContents
    End With
     
    Worksheets("Startside").Select
    Range("A1").Select
End Sub

The actual button is made from a rectangular figure (if that has got anything to do with it..)..

Eivind

[Bob Phillips]

If it is just a shape, then try

        ws.Shapes("Button 1").Delete

[eivindv]

I get a run-time error saying that it can't find the element with the given name + the shape/button is still copied on to the new sheets, even though the text on the shape/button is "Button 1" and I have that in my code.

Any ideas?

[Bob Phillips]

It can't be called Button 1 if you get that error.

[eivindv]

Hm, it looks like I get the same error even if I call it something else. Do you have a correct way to create a VBA button that the line of code will work with (to make sure I do it correctly..)?

[Bob Phillips]

That code works with a shape from the Insert>Shapes function, I tested it. The first code worked with a button from the forms toolbar, and I tested that.

[eivindv]

Ok, and it is correct to put the line where I have put it in the code? Are there any specific way to give names to shapes/buttons? I go to insert>Shapes>add a shape, then double-click the shape and call it e.g. Button. Then I right-click the shape and adds the macro to this shape. Then I press the shape with the macro and the macro runs, but I still get the error message saying that it cant find the element with the given name..

[Bob Phillips]

Where do you enter the name for the shape, in the Name Box to the left of the formula bar?

[eivindv]

No, I wrote it in the actual box, not the Name Box where it was supposed to be written......

Sorry, just my excel-ignorance.. it works totally fine when I write the name to the left of the formula bar

Thanks again for good help, I'm learning for every post!

Eivind

[Bob Phillips]

Thanks again for good help, I'm learning for every post!

You can't ask for much more than that can you