VBA to Copy a worksheet from a template, then increment a drop down list in each new copy by one.

[Huples_Cat]

Hi,

A proper novice when it comes to VBA, i have tried looking for a solution to my issue, but keep running into...issues.

I have a template worksheet, with two drop down lists, one for month and one for profit centre. I have about 80 profit centres and rather than manually copy the tab, change the drop down list for the profit centre and repeat, i hoped a macro could do that for me based on the profit centre list in my "Lookup" sheet (With the list of Profit centres on that sheet going down from cell T4).

I currently have calculations set to manual, as this is a linked file which i assume makes no difference, and whilst i have tried the below based on a previous thread it copies the template sheet once (Template (2)) then goes to debug complaining about "ActiveSheet.Name = c.Value"

Sub Populate()
'
' Populate Macro
'
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Lookup")
For Each c In sh2.Range("T4", sh2.Cells(Rows.Count, 2).End(xlUp))
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Next
'
End Sub

There are no blanks in my list, well until you get to the end of the list, so i have no idea what could be causing it. Additonally i tried one that put the data from the list into cell c2 of the new copied template (where there is a drop down linking to the same list) in the hopes it would generate 80 odd tabs, each named incrementally from list and with c2 cell in each also named incrementally form the list.

Any ideas?

Many Thanks

 

[kevin9999]

For a start, change the 2 to 20 in (Rows.Count,20).End(xlUp))
and see if that makes a difference.

 

[Huples_Cat]

Hi,

Yes thank you that worked! I know not why. I tried pointing it to another, smaller, list (starting at Z4 rather than T4) and it didn't work - was going to give the option of a fully expanded (at op unit) level and a summarized Node view.

If it's not too cheeky of me to ask, do you know how i can get a cell (c2) in each of the newly generated sheets to display the name name of the tab?

 

[kevin9999]

that worked! I know not why.

Because the parameter (2 or 20) refers to the column. Your original was pointing to column "B" (2) and I simply changed it to "T" (20). You can also simply use the Column itself - "T" - instead of the column number.

how i can get a cell (c2) in each of the newly generated sheets to display the name name of the tab?

Assuming the code you originally posted (largely) works for you, try the following (UNTESTED)

Sub Populate()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
    Set sh1 = Sheets("Template")
    Set sh2 = Sheets("Lookup")

    For Each c In sh2.Range("T4", sh2.Cells(Rows.Count, "T").End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = c.Value
            .Range("C2") = c.Value
        End With
    Next
End Sub

 

[Huples_Cat]

Excellent, thank you - i think i can cobble together the rest of what i need from this site, really appreciate your help and patience.

 

[kevin9999]

Excellent, thank you - i think i can cobble together the rest of what i need from this site, really appreciate your help and patience.

Happy to help, and thanks for the feedback

 

[Huples_Cat]

Out of interest, if i wanted to put a value from, say column U rather than T in C2 of the generated worksheets i'd have to do something with the Name = c.Value part?

 

[kevin9999]

That particular line:

Name = c.Value

is what names the sheet. If you wanted to name the sheet according to what was in that row - but in column U then change it to:

Name = c.Offset(0,1).Value

If you wanted to put the value from column U into C2 of the generated sheet, then change this line:

.Range("C2") = c.Value

To this:

.Range("C2") = c.Offset(0,1).Value

 

[Huples_Cat]

Thank you - i did figure it out but appreciate the explanation. To think, i once did 'normal' BASIC at school (many years ago!)

 

[Huples_Cat]

Ok, i swear my last question on this topic! The values i'm coping into C2 from the List in column T are op units and some have a leading zero that VBA is cutting off (i hadn't noticed as i was checking at higher level nodes which are all simply letters), e.g so 0300001916 becomes 300001916 even if i put a ' infront of it, or tinker with the format. Anyway i can amend the above to keep the leading zero?