SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!
Course Of The Month
7 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Creating New Sheets based on list and template
Posted on 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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
4
10 Comments
Active 1 day ago
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
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
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
Active 1 day ago
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.
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?
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?
Active 1 day ago
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
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)?
for example, I tried renaming again by but it renamed in different order (it renamed the LIST and BASE tabs)?
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.
Active 1 day ago
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
Featured Post
RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Excel - Summing a column based on criteria from another column | 5 | 48 | |
| Custom Checklist creation | 2 | 48 | |
| how can I erase a line of text within Microsoft Excel 2013 and replace it with something else? | 4 | 27 | |
| Zip Codes Excel Spreadsheet | 4 | 22 |
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses
Course of the Month7 days, 4 hours left to enroll
732 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.