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