• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

Copy a worksheet & rename from cell value

Hello Experts,

I'd like a macro to:
1)  copy the current worksheet to the end of the workbook  (number of worksheets is variable)
2a) Use the name from N1:T1 cell (a merged field) to rename the worksheet
2b) if the worksheet name is already used, append 1 or 2 or 3 etc.

A sample file is attached.

Thanks
InfoBook.xlsx
0
bikeski
Asked:
bikeski
  • 3
  • 3
1 Solution
 
Martin LissRetired ProgrammerCommented:
You can use this macro.

Sub AddSheet()

    Dim lngSheets As Long
    Dim intCount As Integer
    
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    
    For lngSheets = 1 To Sheets.Count
        If Left$(Sheets(lngSheets).Name, Len(Range("N1"))) = Range("N1") Then
            intCount = intCount + 1
        End If
    Next
    If intCount > 0 Then
        ActiveSheet.Name = Range("N1") & intCount
    Else
        ActiveSheet.Name = Range("N1")
    End If
       
End Sub

Open in new window

0
 
bikeskiAuthor Commented:
Thanks Martin, works great!
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bikeskiAuthor Commented:
Martin, You did as I asked. After applying the macro to several worksheets, I realized I needed to copy everything except the formulas, there I only want the numbers. Let me know how you'd like to proceed, I can open a new question?
0
 
Martin LissRetired ProgrammerCommented:
Sure. Let me know when you've added the question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now