eivindv
02-19-2014, 07:45 AM
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
(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