Home

Index Matches Won't Update After Running Macro More than Once

%3CLINGO-SUB%20id%3D%22lingo-sub-782359%22%20slang%3D%22en-US%22%3EIndex%20Matches%20Won't%20Update%20After%20Running%20Macro%20More%20than%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-782359%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20macro%20in%20Excel%20that%20creates%20template%20worksheets%20from%20a%20list.%26nbsp%3B%20It's%20fantastic...%20except%20that%20if%20I%20run%20the%20macro%20more%20than%20once%20to%20create%20additional%20template%20worksheets%2C%20the%20formulas%20on%20these%20new%20template%20worksheets%20won't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVlookups%20and%20references%20to%20cells%20on%20other%20tabs%20work%20fine.%26nbsp%3B%20But%2C%20Index-Matches%20do%20not%20display.%26nbsp%3B%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-782359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788042%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Matches%20Won't%20Update%20After%20Running%20Macro%20More%20than%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788042%22%20slang%3D%22en-US%22%3EWhat%20do%20you%20mean%20by%20do%20not%20display%3F%20INDEX%2FMATCH%20should%20work%20just%20as%20good%20as%20VLOOKUP.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788100%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Matches%20Won't%20Update%20After%20Running%20Macro%20More%20than%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3BI%20have%20a%20macro%20that%20creates%20new%20tabs%20from%20a%20series%20of%20template%20tabs.%26nbsp%3B%20On%20each%20template%20tab%20is%20a%20drop-down%20menu%20that%20I%20can%20use%20to%20pull%20data%20from%20any%20other%20tab%20in%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20run%20the%20macro%20once%20to%20create%20a%20series%20of%20tabs%20(A%2C%20B%2C%20C%2C%20for%20instance)%2C%20the%20index%20%2F%20match%20functions%20associated%20with%20the%20drop-down%20menu%20work%20just%20fine.%26nbsp%3B%20However%2C%20if%20I%20run%20the%20macro%20again%20to%20create%20additional%20tabs%20(D%2C%20E%2C%20F)%2C%20the%20index%20%2F%20match%20functions%20associated%20with%20drop-down%20menus%20on%20these%20tabs%20will%20not%20work%20(i.e.%20will%20not%20reference%20data%20from%20any%20other%20tab%20in%20the%20worksheet).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788187%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Matches%20Won't%20Update%20After%20Running%20Macro%20More%20than%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788187%22%20slang%3D%22en-US%22%3E%3CP%3EWithout%20seeing%20the%20code%20this%20is%20impossible%20to%20answer%20I'm%20afraid.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-788930%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Matches%20Won't%20Update%20After%20Running%20Macro%20More%20than%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-788930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3Bplease%20see%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton2_Click()%3CBR%20%2F%3EDim%20sh1%20As%20Worksheet%2C%20sh2%20As%20Worksheet%2C%20b%20As%20Integer%2C%20c%20As%20Range%2C%20templname%20As%20String%3CBR%20%2F%3ECreateNew%20%3D%201%3CBR%20%2F%3Eb%20%3D%209%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3ESet%20sh2%20%3D%20Sheets(%22MASTER%20LIST%22)%3CBR%20%2F%3EFor%20Each%20c%20In%20sh2.Range(%22A9%22%2C%20sh2.Cells(Rows.Count%2C%201).End(xlUp))%3CBR%20%2F%3ECreateNew%20%3D%201%3CBR%20%2F%3EFor%20Each%20ws%20In%20Worksheets%3CBR%20%2F%3EIf%20ws.Name%20%3D%20c.Value%20Then%3CBR%20%2F%3ECreateNew%20%3D%200%3CBR%20%2F%3EExit%20For%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EIf%20sh2.Cells(b%2C%20%22A%22)%20%3D%20%22%22%20Then%3CBR%20%2F%3EExit%20For%3CBR%20%2F%3EElse%3CBR%20%2F%3EIf%20CreateNew%20%3D%201%20Then%3CBR%20%2F%3Etemplname%20%3D%20sh2.Cells(b%2C%20%22B%22)%3CBR%20%2F%3ESet%20sh1%20%3D%20Sheets(templname)%3CBR%20%2F%3Esh1.Visible%20%3D%20True%3CBR%20%2F%3Esh1.Copy%20after%3A%3DSheets(Sheets.Count)%3CBR%20%2F%3EActiveSheet.Unprotect%20Password%3A%3D%22%22%3CBR%20%2F%3EActiveSheet.Name%20%3D%20c.Value%3A%20ActiveSheet.Range(%22A9%22)%20%3D%20c.Value%3CBR%20%2F%3EActiveSheet.Protect%20Password%3A%3D%22%22%3CBR%20%2F%3Esh1.Visible%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3Eb%20%3D%20b%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-789625%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Matches%20Won't%20Update%20After%20Running%20Macro%20More%20than%20Once%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789625%22%20slang%3D%22en-US%22%3EThere%20is%20nothing%20in%20this%20code%20that%20might%20explain%20your%20problem.%20Is%20there%20a%20way%20that%20you%20can%20share%20your%20workbook%20without%20sensitive%20information%20in%20it%3F%3C%2FLINGO-BODY%3E
warrevar
Occasional Contributor

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!

5 Replies
What do you mean by do not display? INDEX/MATCH should work just as good as VLOOKUP.

@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).

Without seeing the code this is impossible to answer I'm afraid.

@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

 

 

There is nothing in this code that might explain your problem. Is there a way that you can share your workbook without sensitive information in it?
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies