×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How do I copy a worksheet and change its name?

How do I copy a worksheet and change its name?

How do I copy a worksheet and change its name?

(OP)
I have some template worksheets in a workbook that I want to copy. The templates names start with "zz" to differentiate them from the other worksheets. I want to copy worksheet named "zzThisworksheet" to "Thisworksheet" and leave the "zzThisworksheet" unchanged. The code below does make the copy called "Thisworksheet" but it also renames "zzThisworksheet" to "zzThisworksheet (2)". Any ideas on how to fix this? (There are other template worksheets that are not visible, so that's why I have that one if statement).

Application.ScreenUpdating = False
For Each Wksht In ThisWorkbook.Worksheets
    If Left(Wksht.Name, 2) = "zz" Then
        If Wksht.Visible = True Then
            L1 = Len(Wksht.Name)
            LastSheetName = "Sheet2"
            Workbooks(ThisWorkbook.Name).Activate
            Workbooks(ThisWorkbook.Name).Worksheets(Wksht.Name).Visible = True
            Workbooks(ThisWorkbook.Name).Sheets(Wksht.Name).Activate
            Workbooks(ThisWorkbook.Name).Sheets(Wksht.Name).Select
            Workbooks(ThisWorkbook.Name).Sheets(Wksht.Name).Copy After:=Workbooks(ThisWorkbook.Name).Sheets("Formato")
            Sheets(Wksht.Name).Name = Right(Wksht.Name, L1 - 2)
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

RE: How do I copy a worksheet and change its name?

Try this:

CODE

Dim Wksht As Worksheet, NewSht As Worksheet, LastSheet As Worksheet
Application.ScreenUpdating = False
Set LastSheet = ThisWorkbook.Sheets("Formato")

For Each Wksht In ThisWorkbook.Worksheets
    If Left(Wksht.Name, 2) = "zz" Then
        If Wksht.Visible = True Then
            Wksht.Copy After:=LastSheet
            Set NewSht = ThisWorkbook.Worksheets(LastSheet.Index + 1)
            On Error Resume Next    
            'if the sheet name exists, Error 1004 gets raised
            'abort renaming by resuming execution.
            NewSht.Name = Mid(Wksht.Name, 3)
        End If
    End If
Next

Application.ScreenUpdating = True
Set Wksht = Nothing
Set NewSht = Nothing
Set LastSheet = Nothing
End Sub

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close