Help neede with VBA code for copying and renaming copied sheets

dandas

New member
Joined
Nov 24, 2013
Messages
2
Reaction score
0
Points
0
Hello guys,

I have this two macros, the first is copying sheet "Day 1", the second is naming the sheets 1, 2, 3... and so on, depending on how many sheets want to create. Can you help a little bit, so when you enter the number of sheets, the other macro to rename them without prompting to put name for every sheet.

Thanks



Code:
Sub CreateGamingDay()
ActiveSheet.Unprotect Password:="bidasol"
Application.ScreenUpdating = False
    Dim x As Integer
    
    x = InputBox("Enter number of times to copy the current sheet")
    For numtimes = 1 To x
        'Loop by using x as the index number to make x number copies.
        'Replace "Sheet1" with the name of the sheet to be copied.
    ActiveWorkbook.Sheets("Day 1").Copy _
    After:=ActiveWorkbook.Sheets("Day 1")
    ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Day 1"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Enter gaming day or port name.")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0
    Next
    ActiveSheet.Protect Password:="bidasol"
Application.ScreenUpdating = True
End Sub




Sub ChangeWorkSheetName()
Dim WS As Worksheet
i = 1
On Error Resume Next
For Each WS In Worksheets
WS.Name = "Day " & i
i = i + 1
Next
End Sub
 
Last edited by a moderator:
This is the solution I was looking for



Code:
Sub CreateGamingDay()    
Application.ScreenUpdating = False    
Dim numtimes, x As Integer, ActNm As String        
x = InputBox("Enter number of times to copy the current sheet")    
For numtimes = 1 To x        
'Loop by using x as the index number to make x number copies.        
'Replace "Sheet1" with the name of the sheet to be copied. 
       ActNm = ActiveSheet.Name        
ActiveWorkbook.Sheets("Day 1").Copy After:=ActiveWorkbook.Sheets(ActNm)        
ActiveSheet.Name = "Day " & numtimes + 1 
   Next    
Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:
Back
Top