Dynamic sheet creation button in excel using VB?

[Usva]

I havent used exel too much and even less VB, so my current code might be a bit messy as im building it bit by bit.

Anyways Im trying to make a button that creates a sheet, copies stuff from another sheet and pastes it in the new one, creates a button that takes user from the new sheet back to where the original creation button is and creates new row on that page and button that takes the user from that row to the created page.

Problem is that Im not really sure how to get the name of created sheet to the button on another sheet automatically. Below is the code and the last row is prolly where the problem is. This code doesnt yet have any dynamic position features or such and its just a test untill I get it working properly and then ill be able to use it on the actual excel workbook.

Private Sub CommandButton1_Click()
Sheets("kopioitava").Select
ActiveCell.Range("A1:E8").Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveSheet.Buttons.Add(48, 153, 96, 26.25).Select
Selection.OnAction = "Painike2_Napsautettaessa"
Dim nimi As String
nimi = ActiveSheet.Name
Sheets("toiminnot").Select
ActiveSheet.Buttons.Add(115.5, 221.25, 117, 44.25).Select
Selection.OnAction = Sheets("nimi").Select
End Sub


That code has some finnish words, if u were wondering what they ment. So anyways the last row before End Sub is where the problem is. Its supposed to tell the new button that when its clicked it should take the user to sheet that has the freshly created sheets name. Could someone tell me how to do it right? Currently it doesnt work.

 

[onlyadrafter]

Hello,

Not sure whether this is what you want but it does work (I just don't know if this is what you want).

The code in the relevant sheet is as follows

Public Sub CommandButton1_Click()
Sheets("Sheet1").Select
Range("A1:E8").Copy
'Selection.Copy
Sheets.Add
nimi = ActiveSheet.Name
ActiveSheet.Paste
ActiveSheet.Buttons.Add(48, 153, 96, 26.25).Select
Selection.OnAction = "GOTO_SHEET"
Sheets("Sheet3").Select
ActiveSheet.Buttons.Add(115.5, 221.25, 117, 44.25).Select
Selection.OnAction = "GOTO_SHEET"
End Sub

(this is just your code amended, you will need to change sheet names as appropriate).

In a new module, you need this code

Global nimi As String
Sub GOTO_SHEET()
Sheets(nimi).Select
End Sub


Does this do anything like your requirements?

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[Usva]

Thanks, that helped a bit. But it seems that i need something a bit different. I mean the code works fine and does what i wanted, but when i use it again (press button), it links the previous buttons action to the latest sheet and doesnt leave it to the one i created earlier.

So I guess i would need to know if its possible to make it so that everytime that buttons creates the new sheet and 2 buttons it makes the button that takes user to the new sheet have kind of a permanent effect.

Now when i press the button it doesnt what i needed, but when i press it again, it does what i want but the previous buttons are directed to the latest sheet too.

So what i would need is: press create button at page 1 -> create sheet(1)+button to page 1+button to sheet(1)
press create button at page 1 again-> create sheet(2)+ button to page 1+ button to sheet(2)

I hope thats not too poorly explained. Current code is this:

Public Sub CommandButton1_Click()
Sheets("kopioitava").Select
Range("A1:E8").Copy
Selection.Copy
Sheets.Add
nimi = ActiveSheet.Name
ActiveSheet.Paste
ActiveSheet.Buttons.Add(48, 153, 96, 26.25).Select
Selection.OnAction = "toimintoihin"
Sheets("toiminnot").Select
ActiveSheet.Buttons.Add(115.5, 221.25, 117, 44.25).Select
Selection.OnAction = "GOTO_SHEET"
End Sub

and on module:

Global nimi As String
Sub GOTO_SHEET()
Sheets(nimi).Select
End Sub

Private Sub toimintoihin()
Sheets("toiminnot").Select
End Sub


So that works fine, but i realized that what i need might be something that creates new macro for each button, so that the target of the button wont change every time i press the create button.

So if someone could help me with this a bit more I would be really grateful Thank you in advance.