Macro for deleting sheets
Posted by Tim on May 09, 2000 7:08 PM
I have a workbook which contains a random ammount of sheets, the sheets are created and named from a list of names pulled from a remote database.
When the list is updated any new names have a sheet created for them. My problem arrises in that I need any sheets that do not correspond to a name on the list deleted, and I am not sure how to do that.
Lets say that the list of names is on a sheet named "names" in range A1 - A(x)..... Any thoughts on how I can activate each sheet, compare it's name to the list, and delete it if it is not there?
Posted by Ruth Ann Francis on May 10, 2000 4:38 AM
The basic commands that you need are:
'Determine the number of sheets
number_of_sheets = ActiveWorkbook.Sheets.count
'activate a specific sheet of number current_sheet_number
ActiveWorkbook.Sheets(current_sheet_number).Activate
'turn the alerts off, delete the current sheet and then
'turn the alerts back on.
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
I would first read the names of the sheets you
want to keep into an array.
(dimension the array first: dim array_of_sheet_names(0,2000)
then
do while sheet_name_to_add <> ""
array_of_sheet_names(0,sheet_number_to_add) = sheet_name_to_add
sheet_number_to_add = sheet_number_to_add + 1
sheet_name_to_add = range("A" & sheet_number_to_add)
loop
Then cycle through the sheets comparing the name
to each of the names in your array_of_sheet_names.
If it doesn't match, delete that sheet and move on to
the next one.
You might want to use a do and and if statement with the condition
current_sheet_name <> array_of_sheet_names(array_counter)
You'll probably have to fiddle with the current_sheet_number
counter, as the number of sheets you've got is going to change
each time you delete a sheet.
Hope this helps
Ruth ann
Posted by Tim on May 10, 2000 4:03 PM
Thank you very much Ruth Ann. Very good information. My problem though is that I am not very good at manipulating arrays. I've gotten as far as creating and filling 2 string array variables. One which contains the names of all the worksheets, and one that contains just the names of the worksheets I want to keep. After that I am lost.
