Results 1 to 2 of 2

Thread: Help neede with VBA code for copying and renaming copied sheets

  1. #1

    Help neede with VBA code for copying and renaming copied sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Zack Barresse; 2013-11-27 at 10:26 PM. Reason: Added CODE tags

  2. #2
    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 Zack Barresse; 2013-11-27 at 10:27 PM. Reason: Added CODE tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •