Consulting

Results 1 to 7 of 7

Thread: Copy and rename multiple sheets

  1. #1

    Copy and rename multiple sheets

    I have this code that copies the active worksheet 2x. The original worksheet name is L-2019. I have userform combo box (cboYear ) populated by the year. I would like to rename the 2 new sheets as L-#### and V-#### respectively (#### = cboYear). How can I accomplish this?

    Dim wks as worksheet
    
    For x = 1 To 2    ActiveSheet.Copy After:=Sheets("Security")
    Next
    
    wks.name = cboYear

    here is my failed attempt...

            For x = 1 To 2
                ActiveSheet.Copy After:=Sheets("Security")
                If Right(ActiveSheet.name, 3) = "(2)" Then ActiveSheet.name = "L-" & cboYear
                If Right(ActiveSheet.name, 3) = "(3)" Then ActiveSheet.name = "V-" & cboYear
            Next
    Thank you kindly

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
        Dim myYear As String
        
        myYear = cboYear.Value
        If Evaluate("isref('L-" & myYear & "'!a1)") Then Exit Sub
    
        With Sheets("temlate")
            .Copy After:=Sheets("Security")
            ActiveSheet.Name = "L-" & myYear
             .Copy After:=ActiveSheet
            ActiveSheet.Name = "V-" & myYear
        End With

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    When you copy an ActiveSheet, the copy becomes the ActiveSheet.
    Try this
    Dim wks as worksheet
    Set wks = ActiveSheet
    
    With wks
        .Copy After:=Sheets("Security")
        ActiveSheet.name = "L-" & cboYear
    
        .Copy After:=Sheets("L-" & cboYear)
        ActiveSheet.name = "V-" & cboYear
    
        .Activate
    End With
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Sam...I merged some coding into your coding and I'm trying to eliminate the redundancy.

    Currently...

            With wks
                .Copy After:=Sheets("Security")
                With ActiveSheet
                    .name = "L-" & cboYear
                    
                     ....... (More code)
    
                    .Range("O3") = cboYear & " YTD ROI (%):"
                    .Range("P1:P3") = "$0.00"
                End With
                
                .Copy After:=Sheets("L-" & cboYear)
                With ActiveSheet
                    .name = "V-" & cboYear
                    
                     ....... (More Code)
    
                    .Range("O3") = cboYear & " YTD ROI (%):"
                    .Range("P1:P3") = "$0.00"
                End With
            End With
    I've tried this way, but the code renames first created sheet from L-#### into V-####. The second sheet created is name L-#### (2)

            With wks
                .Copy After:=Sheets("Security")
                With ActiveSheet
                    .name = "L-" & cboYear
                    
                     ....... (More code)
    
                    .Range("O3") = cboYear & " YTD ROI (%):"
                    .Range("P1:P3") = "$0.00"
                End With  
              
                With ActiveSheet
                    .Copy After:=Sheets("Security")
                    .name = "V-" & cboYear
                End With
            End With

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
                With ActiveSheet
                    .Copy After:=Sheets("Security")
                    .name = "V-" & cboYear
                End With
    Expanding that, one gets
    Sheets("L-1999").Copy After:=Sheets("Security")
    Sheets("L-1999"). Name = "V-1999"
    And you're still left with a sheet named ""L-1999" (2)"
    It works like that because "With Active Sheet" means With the sheet that is active when this line executes, regardless of what else happens before the End With.


    Because ActiveSheet is volatile, I avoid it like the plague
        wks .Copy After:=Sheets("Security")
        ActiveSheet.name = "L-" & cboYear
    
        Set wks = Sheets("L-" & cboYear)
        With wks        
             ....... (More code)
    
             .Range("O3") = cboYear & " YTD ROI (%):"
             .Range("P1:P3") = "$0.00"
             .Copy After:=Sheets("Security"): ActiveSheet.name = "V-" & cboYear
             .Tab.Color = vbGreen 
        End With
     
        Sheets("V-" & cboYear).Tab.Color = vbRed
    Even your failed attempt in post #1 would work like
            For x = 1 To 2
                ActiveSheet.Copy After:=Sheets("Security")
                If x = 1 Then ActiveSheet.name = "L-" & cboYear
                If x = 2 Then ActiveSheet.name = "V-" & cboYear
            Next
    This works because the ActiveSheet line is executed twice

    The reason it failed the way you wrote it is because there was no sheet name ending in "(3)"
    Last edited by SamT; 11-12-2019 at 01:53 AM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    Thank you everyone for the time an explanation

    Cheers

  7. #7
    Thank you for the explanation SamT.

Posting Permissions

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