Solved

Creating New Sheets based on list and template

Posted on 2016-08-31
10
31 Views
Last Modified: 2016-08-31
Hello,

I am looking for an Excel solution.

If I have a base worksheet template (which can change), I need to create sheets identical to the template for a specific list.

Each sheet will be named according to the list and a cell in each of the sheets to be named according to the list also.

The attached file is what I am looking for. For example, sheets will be generated (based on "BASE" template) and named according to column "D" and each sheet will have a named cell according to column "E".

When finished, the list would need to be deleted and just the sheets remaining.

Example excel attached.

thanks in advance
test_newsheet.xlsx
0
Comment
Question by:forever7
  • 6
  • 4
10 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41778983
Try this macro...

Sub CreateSheets()

    Dim tmpSheet As Worksheet, lstSheet As Worksheet
    Dim rw As Long
    
    Set tmpSheet = Worksheets("BASE")
    Set lstSheet = Worksheets("LIST")
    
    For rw = 4 To lstSheet.Cells(Rows.Count, "D").End(xlUp).Row
        tmpSheet.Copy After:=Worksheets(Worksheets.Count)
        With ActiveSheet
            .Name = lstSheet.Cells(rw, "D")
            .Range("E1").Value = lstSheet.Cells(rw, "E")
        End With
    Next
    Application.DisplayAlerts = False
    lstSheet.Delete
    Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:forever7
ID: 41779013
Thank You Wayne,

If I want to keep the main sheet, what would the edited code be?

Also, to delete the code if the workbook gets passed on do you open VB editor and delete "module 1" (if that's what I saved it as).

thanks
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41779021
If I want to keep the main sheet, what would the edited code be?

To not delete the LIST sheet, remove lines 16-18.

Also, to delete the code if the workbook gets passed on do you open VB editor and delete "module 1" (if that's what I saved it as).

Yes, or you can save it as ".xlsx" which is a macro free workbook.
0
 

Author Comment

by:forever7
ID: 41779041
Thanks - this is just what I need.

one last thing if possible

If the sheets are required to have a different name, is there a way to rename the sheets that have been created with another name based on another column say "E".

For example, London No1, London No2... need to be renamed to Lon No1, Lon No2 etc..

This may occur after populating the created sheets with data?
0
 

Author Comment

by:forever7
ID: 41779051
should have said ...another column say "F".
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41779101
You can use the below macro to rename the sheets based on the values in column "F" on the LIST sheet...

Sub RenameSheets()

    Dim lstSheet As Worksheet, ws As Worksheet, rw As Long
    
    Set lstSheet = Worksheets("LIST")
    
    For rw = 4 To lstSheet.Cells(Rows.Count, "D").End(xlUp).Row
        On Error Resume Next
        Set ws = Worksheets(lstSheet.Cells(rw, "D").Value)
        If Not ws Is Nothing Then
            ws.Name = lstSheet.Cells(rw, "F")
        End If
        On Error GoTo 0
    Next
    
End Sub

Open in new window

0
 

Author Comment

by:forever7
ID: 41779128
The rename works, but if I want to rename again to  something else, is there a way to rename a sheet to another sheet based on the list.

for example, I tried  renaming again by but it renamed in different order (it renamed the LIST and BASE tabs)?
0
 

Author Comment

by:forever7
ID: 41779133
I may need to generate around 30-40 sheets. This a great help but if I can get the renaming correct then I can generate sheets, rename them if asked. and possibly rename again if things change.
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41779158
Instead of using the Worksheet names, use the values in cell E1....

Sub RenameSheets()

    Dim lstSheet As Worksheet, ws As Worksheet, rw As Long
    
    Set lstSheet = Worksheets("LIST")
    
    For rw = 4 To lstSheet.Cells(Rows.Count, "D").End(xlUp).Row
        For Each ws In Worksheets
            If ws.Range("E1").Value = lstSheet.Cells(rw, "E").Value Then
                ws.Name = lstSheet.Cells(rw, "F")
                Exit For
            End If
        Next
    Next
    
End Sub

Open in new window

0
 

Author Closing Comment

by:forever7
ID: 41779161
Wayne - thanks very much - this is exactly what I needed. Great help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to Auto-fill data in Database 2 19
Three Dimensional Journey Map in Excel 8 28
TT Status Chang 3 32
Column Formatting 4 34
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

12 Experts available now in Live!

Get 1:1 Help Now