Copy and rename tab based on list

[crocco]

Hi all,

I have a tab called ‘template’

I have a tab called ‘products’ with a columnar list of text strings starting in cell A2.

I would like some VBA that copies and renames the ‘template’ tab for each item in the string list starting on cell A2 on the ‘products’ tab.

For example:

If products tab cell A2 = “Apples” and products tab cell A3 = “Pears”. The VBA I need would copy the ‘template’ tab twice and rename the tabs ‘Apples’ and ‘Pears’ respectively.

I will be left with three identical tabs called ‘template’, ‘apples’ and ‘pears’ (and the originals ‘products’ tab too).

I would like this to work for any number of items in the list on the ‘products’ tab.

Thanks!

 

[jmacleary]

Try this:

Sub MakeTabs()
'
' Macro9 Macro
'

'
Dim fulllist() As Variant
 LastRow = Sheets("Products").Cells(Sheets("Products").Rows.Count, "A").End(xlUp).Row
fulllist = Sheets("Products").Range("A2:A" & Format(LastRow)).Value
For Each newname In fulllist

    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets("Template (2)").Name = newname
Next newname
End Sub

It will fail if any tab already exists with the names. You might want to blank out the list at the end as well.

 

[Joe4]

Here is some code that will do that, that also has some error handling to alert you of invalid sheet names (either because they already exists or use illegal characters):

Sub MyCopyMacro()

    Dim lr As Long
    Dim r As Long
    Dim newtabname As String
    
    Application.ScreenUpdating = False
    On Error GoTo err_check
    
'   Find last row with data in column A on products sheet
    lr = Sheets("products").Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all entries
    For r = 2 To lr
'       Get new sheet name
        newtabname = Sheets("products").Cells(r, "A")
'       Insert new sheet and name
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = newtabname
'       Copy from template
        Sheets("template").Select
        Cells.Select
        Selection.Copy
        Sheets(newtabname).Select
        ActiveSheet.Paste
    Next r

    On Error GoTo 0
    Application.ScreenUpdating = True
    MsgBox "Process complete"
    Exit Sub

err_check:
    Select Case Err.Number
        Case 1004
            MsgBox "'" & newtabname & "' is not a valid sheet name or already exists in the workbook.", vbOKOnly, "ERROR!!!"
        Case Else
            MsgBox Err.Number & ": " & Err.Description
    End Select

    Application.ScreenUpdating = True

End Sub

 

[BlueAure]

Might as well throw mine in too.

    Sub Sub1()
    Dim sheetNames As Variant
    Dim i As Integer
    Dim ws1 As Worksheet
    Dim col As Integer
    
    'Column of the names
    col = 1
    
    'Change the range here to the range of your sheet names
    sheetNames = ThisWorkbook.Sheets("START").Range("A1:A7").Value
    
    For i = 1 To UBound(sheetNames)
        
        If Not sheetNames(i, col) = "" Then
            Set ws1 = ThisWorkbook.Worksheets("template")
            ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
            ThisWorkbook.Sheets("template (2)").Name = sheetNames(i, col)
        End If
    Next i






End Sub

 

[Joe4]

BlueAure,

Just a few comments on your code, especially this line:

sheetNames = ThisWorkbook.Sheets("START").Range("A1:A7").Value

Note the following things they said in the original post:

I have a tab called ‘products’ with a columnar list of text strings starting in cell A2.

So you want to use the sheet name "products", not "START".

I would like this to work for any number of items in the list on the ‘products’ tab.

Your code goes exactly from A1:A7 (is not dynamic), instead of from A2 to wherever it may end.
You can look at the code jmacleary and I posted that shows how to dynamically find the last row in a column with data.

 

[BlueAure]

BlueAure,

Just a few comments on your code, especially this line:

Note the following things they said in the original post:

So you want to use the sheet name "products", not "START".


Your code goes exactly from A1:A7 (is not dynamic), instead of from A2 to wherever it may end.
You can look at the code jmacleary and I posted that shows how to dynamically find the last row in a column with data.

Thank you Joe4, unfortunately I can no longer edit my answer. I was testing on one of my existing workbooks and forgot to change the sheet name. In regard to being dynamic, I always prefer code that is easy to understand and edit myself. With a set range, I know that I can adjust that to whatever I need rather than struggle to change a line that I don't understand how it works.

Thank you for the corrections.

 

[Joe4]

I always prefer code that is easy to understand and edit myself.

Many times the user isn't proficient in VBA, or will be distributing to users you are not proficient in VBA. So they really cannot expect (or want) them to edit the VBA code every time they run it to suit their needs. A well-designed VBA program should require users to constantly adjust the code. At the very least, it would be better to have the VBA code to prompt the user to enter the range they want to apply it to, and use that entry in the code.

The code for dynamically finding the last row is actually not that complex. The ".End(xlUp)" is just the VBA equivalent of using CTRL + Up Arrow, if you have ever used those keyboard shorcuts to find the end of your data. Years ago, it was quite common to see code like this:

Range("A65536").End(xlUp)

to find the last row in column A with data, as 65536 was the last possible row in Excel, until the newer versions of Excel with over a million rows.
Rows.Count counts the maximum number of rows available in the used version of Excel, so will work regardless of the version of Excel being used.
And I imagine that you are probably familiar with using Cells(row,column) instead of Range(...) to reference ranges in Excel.

So, if we put it all together, it looks like this:

lastRow = Cells(Rows.Count,"A").End(xlUp).Row

we use the .Row property to get the row number of the range we are ultimately selecting.

This is one of the most commonly used bits of code that I use, so it is a good one to learn!

 

[jmacleary]

Thanks Joe4. BlueAure you say

With a set range, I know that I can adjust that to whatever I need

- this is impossible if the range you are wanting to work with is a result of a database query, or indeed any other operation which results in a variable number of lines. I think you should at least take on board the code in Joe4's last post for finding the end of a range.

 

[BlueAure]

Maybe it's just personal preference. Personally, I'd much rather be given tools to build my solution rather than the solution itself. If someone gave me something that automatically grabs a set of values from the 2nd row to the end of available data in that column, I'd say its more difficult to edit a "one-liner" than a set of lines that may be more efficient in one instance but also more flexible to apply to other problems later on.

this is impossible if the range you are wanting to work with is a result of a database query, or indeed any other operation which results in a variable number of lines

If it was my preference here, I would probably use that exact line to read these cells into an array and do all my work with that, however, I didn't want to over-engineer a potentially simple solution that could also double as an educational opportunity. Call it giving someone a hammer and teaching them how to hit a nail rather than giving them a general contractor and teaching them how to order them around. Each one ends up with a built house, one is far more approachable to learn and provides a grounding to someday build a house themselves. If someone is working with a high level of competency, I would rather give them the tools for the job as opposed to doing it for them, to build their skill and give them an understanding of the tool that they wouldn't develop if simply given a solution. Learn more by doing, as they say.

With this question, I assumed a competency that has an understanding of ranges and Excel's format of addressing them. By using functions that they are most likely already familiar with, the asker could build that code snippet into a more complex solution that solves their problem way better than I could, because they know their requirements more intimately than I do. When I was learning, I hated "Black-Box" code that did what I wanted, but couldn't adapt to other situations.

I am not calling either of your snippets unreadable or un-modifiable, just trying to clarify my approach to answering. Was it my best solution? No. Was it understandable and can it be used to learn? I would hope so. Even if crocco used someone else's code, I would hope they saw and understood the different approaches taken by all of us and keep them in mind the next time they have an application that could benefit from it.

Thanks for the impromptu explanation of .End(xlUp)!

 

[Joe4]

FWIW, your code assumes that users understand loops and arrays, and one could argue that most inexperienced VBA users won't know those any better than they will the code to find the last row.

From my many years here, one thing that I have picked up along the way is that users like solutions that they don't need to mess with, especially if they are unfamiliar with VBA code.
In general, most users here fall into two categories:
1. Ones that just want a solution, and don't care how it works (and have no desire to try to understand it)
2. Ones that want to understand how the solution they get works

Towards the second, I try to document my code a bit (sometimes, I do better than others), to explain what each section does, and I usually don't try to cram everything into a few complex rows, for the sake of making my code a few lines shorter (i.e. some people will put the last row calculation right in the "For..." loop line, which makes the code a line or two shorter, but makes that one line much more complex).

And I am always happy to explain my solution in more detail to those who want to understand it better. I love it when people want to understand it as well as use it!