Jul 31 2019 01:29 PM
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?
Thanks!
Aug 05 2019 05:42 AM
Aug 05 2019 06:12 AM
@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).
Aug 05 2019 07:09 AM - edited Aug 05 2019 08:13 AM
Without seeing the code this is impossible to answer I'm afraid.
Aug 05 2019 12:34 PM
@Jan Karel Pieterse please see below...
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
Aug 06 2019 01:23 AM