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

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:

Code:

---

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

Code:

---

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

---

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

Excellent, thank you!

Eivind

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

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

Code:

---

        ws.Buttons("Button 1").Delete

---

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:

Code:

---

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

If it is just a shape, then try

Code:

---

        ws.Shapes("Button 1").Delete

---

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?

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

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..)?

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.

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..

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

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

Quote:

---

Originally Posted by eivindv

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

---

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