A macro to create new, copy and name worksheets based on a list
Solved/Closed
chottabeem
Posts
5
Registration date
Tuesday July 19, 2016
Status
Member
Last seen
July 22, 2016
-
Jul 19, 2016 at 02:24 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - May 11, 2017 at 12:52 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - May 11, 2017 at 12:52 AM
Related:
- Excel vba copy sheet and rename based on list
- Copy worksheet multiple times and rename - Best answers
- Excel vba copy sheet and rename based on cell value - Best answers
- Number to words in excel without vba - Guide
- Compare two worksheets and paste differences to another sheet - excel vba free download ✓ - Excel Forum
- Rename computer cmd - Guide
- My contacts list names - Guide
- Enable vba in excel - Guide
11 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Jul 21, 2016 at 01:17 AM
Jul 21, 2016 at 01:17 AM
Hello Chottabeem,
Assuming that you only want to create and name new sheets from the list in the Summary sheet, then the following code will do that:-
If you intend to use just numbers as the sheet names, then you will need to format those numbers as text otherwise, each time that you run the code, you will have additional and unwanted sheets added that will just have sheet numbers as names.
Run the code from the Summary sheet.
I hope that this helps.
Cheerio,
vcoolio.
Assuming that you only want to create and name new sheets from the list in the Summary sheet, then the following code will do that:-
Sub CreateNameNewSheets() Dim LR As Long Dim c As Range Dim ws As Worksheet LR = Range("A" & Rows.Count).End(xlUp).Row For Each c In Range("A2:A" & LR) Set ws = Nothing On Error Resume Next Set ws = Worksheets(c.Value) If ws Is Nothing Then Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value End If Next c End Sub
If you intend to use just numbers as the sheet names, then you will need to format those numbers as text otherwise, each time that you run the code, you will have additional and unwanted sheets added that will just have sheet numbers as names.
Run the code from the Summary sheet.
I hope that this helps.
Cheerio,
vcoolio.
Jul 21, 2016 at 03:06 AM
Thanks for immediate reply. The code works good and creates new worksheets listed in "Summary".
Please refer to my file (shared as link http://speedy.sh/xcHkc/Macro-Temp.xlsm ) there are two sheets"Summary" consists of list and "Sheet1" having data.
The existing "Sheet1" (with data) to be renamed and copied along with data for the list in "Summary" (like, 10, 20, 30, 40.... 200)
Hope I clarified and expect the right solution.