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
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
