Report

A macro to create new, copy and name worksheets based on a list [Solved]

Ask a question chottabeem 5Posts Tuesday July 19, 2016Registration date July 22, 2016 Last seen - Latest answer on Jul 22, 2016 03:00AM
Greetings,
I need a macro that can automatically create new, copy (existing worksheet say '100') and rename the new worksheets, based on a list that exists in another sheet ("Summary") of the same workbook? The list begins from '100' at cell A2. How can this be implemented under Excel?
It will be great-full, if I am be helped with the solution.
See more 
Helpful
+1
moins plus
Hello Chottabeem,

You're welcome. Glad that I could help.

I'll leave it to you to mark this thread as solved.

Cheerio,
vcoolio.
Add comment
Helpful
+0
moins plus
Hello Chottabeem,

Could you please supply a sample of your work book (be careful with any sensitive data) outlining exactly what you would like to do.

Upload a sample to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here.

We should then be able to help you.

Cheerio,
vcoolio.
chottabeem 5Posts Tuesday July 19, 2016Registration date July 22, 2016 Last seen - Jul 21, 2016 12:34AM
Greetings,
Thanks for your reply. I'm posting the link to the sample file.
http://speedy.sh/xcHkc/Macro-Temp.xlsm

Regards
Reply
Add comment
Helpful
+0
moins plus
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:-

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.
chottabeem 5Posts Tuesday July 19, 2016Registration date July 22, 2016 Last seen - Jul 21, 2016 03:06AM
Hi Vcoolio,

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.
Reply
Add comment
Helpful
+0
moins plus
Hello Chottabeem,

I don't follow what you are trying to do. Are you saying that you would like the data in sheet1 transferred to each newly created sheet for each Supplier?

Cheerio,
vcoolio

P.S.
"Hope I clarified and expect the right solution."

Just a tip. Never use the word "expect" in any Forum such as this.
On this Forum, and all others like it, the people who help others such as yourself, are all Volunteers and give their time freely whenever they can to help. Hence, Posters are not in a position to expect anything.

Please familiarise yourself with the Charter.

I trust that you understand.
chottabeem 5Posts Tuesday July 19, 2016Registration date July 22, 2016 Last seen - Jul 21, 2016 05:52AM
Yes

P.S: Noted
Reply
Add comment
Helpful
+0
moins plus
Hello Chottabeem,

I assume that you have answered "yes" to my question. If so, would you not prefer to name each sheet after each Supplier (or at least use a Supplier ID). You would not then need a Summary sheet. You could just use sheet1 as an Input sheet and perhaps name it Data Input.
Any new data that you enter, even for new Suppliers, could be quickly updated with the click of a button.

This would simplify the whole process for you.

Let us know what you think of this approach.

Cheerio,
vcoolio.
Add comment
Helpful
+0
moins plus
Hello Chottabeem,

I have attached your work book (see the link below) with some minor changes which I think you will like:-

https://www.dropbox.com/s/dwpzvk2qsmg3i8m/Chottabeem%28create%20shts.%2C%20transfer%20data%2C%20autofilter%29.xlsm?dl=0

I have added the following code which creates the new sheets and transfers the relevant data to each individual sheet:-

Option Explicit
Sub CreateSheetsCopyData()

        Dim ar As Variant
        Dim i As Integer
        Dim LR As Long
        Dim c As Range
        Dim ws As Worksheet, ws1 As Worksheet

Set ws1 = Worksheets("Data Input")
LR = ws1.Range("B" & Rows.Count).End(xlUp).Row
ar = ws1.Range("B6", ws1.Range("B" & ws1.Rows.Count).End(xlUp))

Application.ScreenUpdating = False

For Each c In ws1.Range("B6:B" & 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

For i = 0 To UBound(ar)
         Sheets(ar(i, 1)).UsedRange.ClearContents
         ws1.Range("B5", ws1.Range("B" & ws1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         ws1.[A5].CurrentRegion.Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(xlUp)
         Sheets(ar(i, 1)).Columns.AutoFit
    Next i
ws1.[B5].AutoFilter
ws1.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Sheets created/Data transfer completed!", vbExclamation, "Status"

End Sub


You may notice that I have re-named sheet1 as "Data Input" and your previous Summary sheet to "List". The "List" sheet simply lists your Suppliers' names and an ID which I randomly created with the numbers that you already had in the sheet. The main reason for placing an "A" in front of the numbers is so that the IDs are recognised as text otherwise the code will falter.

In the "Data Input" sheet, I have inserted a column (named Supplier ID) before the Suppliers Name column and placed the Supplier IDs in this column. You can change the IDs to suit yourself but remember to include some text in them.

As some of your Suppliers' names surpass the 31 character limit for sheet tab names, the code will hence search and filter for the Supplier IDs, create new sheets named after the IDs and then transfer the relevant data for each Supplier to their individual sheet.

You may also notice that I have unmerged the column headings row as merged cells play havoc with VBA coding. As you can see, it hasn't made much difference to your formatting. I have also "unwrapped" all text over the entire sheet as this was previously randomly placed over the data set for no apparent reason.

In the sample file in the link mentioned above, just click on the "RUN" button to see it all work. The code is in Module1.

I hope that this helps.

Cheerio,
vcoolio.
chottabeem 5Posts Tuesday July 19, 2016Registration date July 22, 2016 Last seen - Jul 22, 2016 02:36AM
Hi Vcoolio,

Thanks a ton. This works good.
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!