Solved

Copy a worksheet & rename from cell value

Posted on 2015-01-17
6
76 Views
Last Modified: 2015-01-19
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
Comment
Question by:bikeski
  • 3
  • 3
6 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40555892
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
 

Author Closing Comment

by:bikeski
ID: 40558551
Thanks Martin, works great!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40558580
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:bikeski
ID: 40558656
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40558678
Sure. Let me know when you've added the question.
0
 

Author Comment

by:bikeski
ID: 40558824
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now