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]

OK, bit more complicated. (please note that this will be my last response to this thread as I'm about to go offline for a couple of weeks)
Try replacing this line:

.Range("C2") = c.Value

with this:

c.Copy
.Range("C2").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False