Copy to added sheet contents of previous and increment by 1

APAL1

New Member
Joined
Jun 22, 2014
Messages
13
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
This should do it.
Code:
Sub CopySheet()
    Application.ScreenUpdating = True
    ActiveSheet.Copy after:=Sheets(Sheets.Count)
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top