Copy sheet to end of workbook by clicking add sheet

Suby

New Member
Joined
Jan 21, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am fairly new to code/macros and don't understand much of it, but by reading a lot of posts and forums was able to duck tape together something I was looking for. I am looking for help to improve on it and streamline it. Any help to improve the 2 problems I would like to improve on would be greatly appreciated!

Basically I have a template of form and every time I click + (add sheet) it makes a copy and adds sheet. If I am not on last sheet, it will not add sheet to the end.
Problem 1:
I would like copy of sheet to be added to end of workbook, no matter what active sheet I am on.

I also have the sheet to be renamed based on cell value. When I change the cell it automatically renames the sheet, prior to being able to do that I receive error "that name is already taken, try a different one". I then have to hit end on error prompt and then proceed to change cell.
Problem 2:
I would like there to be no error. If possible, maybe an input box that prompts user to enter value in that will change the cell that renames the sheet. If not that, maybe some sort of way to reference a cell and then check workbook to find the correct increment to add after.



WHAT I CURRENTLY HAVE
- Sheet1 - "Tracking" sheet at beginning, would like that to stay.
- Sheet2 - "Template" sheet is 2nd sheet, would like that to stay.
- Sheet3 and beyond - Adding sheet copies "Template" and then I get through error to enter in 'item #' and 'increment' in the cell to rename sheet.
Sheet renames off of cell B1. B1 would be in format '0000-000'. 'item #' = 0010 and 'increment' is the 1st so = 001.
Sheet3 should be 0010-001. Sheet 4 should be 0010-002. Sheet 5 should be 0010-003 and so on, as user would click sheet to fill out form.

Code in worksheet template that gets copied as well with each new sheet as added:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("B1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

Code in workbook:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim tmpName As String

tmpName = Sh.Name
Sheets("Template").Copy Before:=Sheets(Sh.Name)
Application.DisplayAlerts = False
Sheets(Sh.Name).Delete
Application.DisplayAlerts = True
Sheets("Template (2)").Name = tmpName
End Sub
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,128,154
Messages
5,629,005
Members
416,358
Latest member
grsaltzman

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
Top