Hello Everyone!
Help appreciated:
I have a template which the user fills, and then clicks a button. It's not a userform, quite unfortunate.
The macro of the button should do this:
Add a worksheet
Copy all features of the previous template (formatting, formulas, values etc.) and rename the new sheet with the same name but incremented by one
It also copies the button with the macro, which should do the same: add, copy, copy buttons, rename with the same name incrementing by one...
and so fourth.
But all this happens only if the button is clicked...
I've tried a lot of stuff, and googled through, but nothing really caught my idea (naturally, every macro has its own life...)
for example, I tried "for each" and then dozens of sheets were created, but the buttons were not copied properly...
I tried a GoTo, to break the loop, but then only one more sheet was created and the error decided that "a name already exists".
I am about to give up and create some quite stupid machine, a terribly ugly solution.
my code basically looks like this (took it from some forum's thread):
<code class=" language-vbnet">Dim f As Worksheet, t As Worksheet
Application.ScreenUpdating = True
Set f = ActiveSheet
Set t = Sheets.Add(after:=f)
f.Copy
With t
With .Cells(1)
.PasteSpecial xlValues
.PasteSpecial xlFormats
.Select
End With
.Name = HERE THERE"S A PROBLEM
End With
Application.CutCopyMode = False
f.Activate
Application.ScreenUpdating = True
</code>it's an almost good solution, but I miss the buttons and some loop that will work only if the button "ADD" is clicked
Any ideas? Please assist
Thanks in advance!
This forum helped me more than once, maybe also this time i'll succeed
Help appreciated:
I have a template which the user fills, and then clicks a button. It's not a userform, quite unfortunate.
The macro of the button should do this:
Add a worksheet
Copy all features of the previous template (formatting, formulas, values etc.) and rename the new sheet with the same name but incremented by one
It also copies the button with the macro, which should do the same: add, copy, copy buttons, rename with the same name incrementing by one...
and so fourth.
But all this happens only if the button is clicked...
I've tried a lot of stuff, and googled through, but nothing really caught my idea (naturally, every macro has its own life...)
for example, I tried "for each" and then dozens of sheets were created, but the buttons were not copied properly...
I tried a GoTo, to break the loop, but then only one more sheet was created and the error decided that "a name already exists".
I am about to give up and create some quite stupid machine, a terribly ugly solution.
my code basically looks like this (took it from some forum's thread):
<code class=" language-vbnet">Dim f As Worksheet, t As Worksheet
Application.ScreenUpdating = True
Set f = ActiveSheet
Set t = Sheets.Add(after:=f)
f.Copy
With t
With .Cells(1)
.PasteSpecial xlValues
.PasteSpecial xlFormats
.Select
End With
.Name = HERE THERE"S A PROBLEM
End With
Application.CutCopyMode = False
f.Activate
Application.ScreenUpdating = True
</code>it's an almost good solution, but I miss the buttons and some loop that will work only if the button "ADD" is clicked
Any ideas? Please assist
Thanks in advance!
This forum helped me more than once, maybe also this time i'll succeed