I am working on a project with MS Excel 2003, using VBA. Part of the
project is adding 34 separate sheets to the workbook, each loaded with
data imported from txt files. I also need and created a procedure to
delete the 34 sheets so they are be reloaded.
The 1st time through, everything works as planned. On the 2nd time
through, the worksheets load fine, it throws a "subscribe out of
range" error during the deletion. What's really weird is the 34
sheets do actually disappear, but any link from them (and there are
lots of them) still display the data from the quasi-deleted sheets.
And something still appears to be running, although I can't kill
it...not even through the "stop" button in the VBA interface. This
runaway process also prevents me from closing Excel. I am force to
use task manager to kill it.
I'll included the deletion code below. I am loading the names of the
34 sheets into an array and then passing array to the
worksheets.delete method. Like I said, this only happens the 2nd time
I do it. The 1st time, it works fine.
Any help or suggestions appreciated.
Sub DeleteExistingDataSheets()
Dim intRow As Integer
Dim shtRefer As Worksheet
Dim sht2delete() As String
intRow = 0
Application.DisplayAlerts = False
Set shtRefer = Sheets("Refer")
' Do Loop creates an array of the names of all of the data sheets
Do While Len(Trim(shtRefer.Cells(intRow + 2, 1))) <> 0
ReDim Preserve sht2delete(intRow)
sht2delete(intRow) = shtRefer.Cells(intRow + 2, 1).Text
intRow = intRow + 1
Loop
Set shtRefer = Nothing
' deletes sheets based on array of names
Worksheets(sht2delete).Delete
Application.DisplayAlerts = True
End Sub
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
