PDA

View Full Version : [SOLVED] Automatically give new sheet name (created frm template w/macro) the value of a cell



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

Bob Phillips
02-19-2014, 09:53 AM
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
02-20-2014, 12:00 AM
Thank you so much for your quick respons! I removed the recorded macro and replaced it with yours :)

Excellent, thank you!

Eivind

eivindv
02-24-2014, 12:44 AM
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
02-24-2014, 01:37 AM
On the copied sheet, add something like this after you rename the sheet


ws.Buttons("Button 1").Delete

eivindv
02-24-2014, 02:01 AM
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
02-24-2014, 02:17 AM
If it is just a shape, then try


ws.Shapes("Button 1").Delete

eivindv
02-24-2014, 02:30 AM
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
02-24-2014, 02:49 AM
It can't be called Button 1 if you get that error.

eivindv
02-24-2014, 03:49 AM
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
02-24-2014, 03:55 AM
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
02-24-2014, 04:08 AM
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
02-24-2014, 05:56 AM
Where do you enter the name for the shape, in the Name Box to the left of the formula bar?

eivindv
02-24-2014, 06:34 AM
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
02-24-2014, 06:51 AM
Thanks again for good help, I'm learning for every post!

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