I am a total newbie with VBA, so this may be a stupid/simple question, but please bear with me...
I set up a spreadsheet with a macro that downloads stock quotes (the bulk of this macro is based on a macro from another spreadsheet that I found online). I discovered that Excel creates a new range name (ExternalData_1, ExternalData_2, etc.) for each block of quote data downloaded (that is, for each ticker symbol). I want to delete these range names each time the macro runs. So I added this to the macro:
Dim n As Name
For Each n In ActiveWorkbook.Names
If Left(n.Name, 13) = "ExternalData_" Then n.Delete
Next
I cannot delete the names individually (using a counter to generate the numerical portion of each name) because I cannot necessarily predict how many quotes will be downloaded, and if the macro tries to delete a name that doesn't exist, it generates an error.
My problem is that this VBA code does not work (it doesn't delete any range names). However, if I create a test spreadsheet, manually create a bunch of named ranges (ExternalData_1, ExternalData_2, etc.), and then run these same commands in a test macro, it works fine! I think my problem is rooted in the fact that my main workbook contains several worksheets, and the ExternalData_xx range names that are created have a scope of only the single worksheet where the stock quotes are being loaded. Whereas in my test file, the range names I manually create have a scope of "Workbook" rather than just one worksheet. I should also note that the macro button (that triggers the stock quote download) is on a different worksheet than the one where the quotes are loaded (and becomes the sole scope of the new range names).
How should I modify my VBA code to actually delete the single-worksheet-scoped range names (ExternalData_1, ExternalData_2, etc.)? And please give me the actual code because if you tell me to "make the other worksheet the active worksheet" (or something like that), I don't know how to do that in VBA code!! Thanks for helping this newbie!
Bookmarks