I created a macro in Excel that creates template worksheets from a list. It's fantastic... except that if I run the macro more than once to create additional template worksheets, the formulas on these new template worksheets won't work.
Vlookups and references to cells on other tabs work fine. But, Index-Matches do not display. Any ideas?
@Jan Karel Pieterse I have a macro that creates new tabs from a series of template tabs. On each template tab is a drop-down menu that I can use to pull data from any other tab in the worksheet.
If I run the macro once to create a series of tabs (A, B, C, for instance), the index / match functions associated with the drop-down menu work just fine. However, if I run the macro again to create additional tabs (D, E, F), the index / match functions associated with drop-down menus on these tabs will not work (i.e. will not reference data from any other tab in the worksheet).
Private Sub CommandButton2_Click() Dim sh1 As Worksheet, sh2 As Worksheet, b As Integer, c As Range, templname As String CreateNew = 1 b = 9 Application.ScreenUpdating = False Set sh2 = Sheets("MASTER LIST") For Each c In sh2.Range("A9", sh2.Cells(Rows.Count, 1).End(xlUp)) CreateNew = 1 For Each ws In Worksheets If ws.Name = c.Value Then CreateNew = 0 Exit For End If Next If sh2.Cells(b, "A") = "" Then Exit For Else If CreateNew = 1 Then templname = sh2.Cells(b, "B") Set sh1 = Sheets(templname) sh1.Visible = True sh1.Copy after:=Sheets(Sheets.Count) ActiveSheet.Unprotect Password:="" ActiveSheet.Name = c.Value: ActiveSheet.Range("A9") = c.Value ActiveSheet.Protect Password:="" sh1.Visible = False End If b = b + 1 End If Next Application.ScreenUpdating = True End Sub