Deleting sheets based on a range

[snipescc]

Hi everyone. I'm pretty new to coding from scratch in VB, and I've got some code that should loop, but it doesn't. The idea eventually will be to download a spreadsheet, make a new sheet for every item in a range, and then filter for each of those items and put it in the right sheet. I have to code to create the sheets (lightly modified from something I got here)

Sub CreateSheets()
'Written by Barrie Davidson
For Each c In Sheets("FilterList").Range("b2:b74")
Sheets.Add
ActiveSheet.Name = Right(c.Value, 30)
Next c

End Sub


That's working well, so I've tried to modify it so that it will also delete sheets based on the same range. I've gotten it to delete the first item in the range, but then it stops. Can anyone see what I've dome wrong?

For Each c In Sheets("FilterList").Range("b2:b74")
Application.DisplayAlerts = False
Worksheets(c.Value).Delete
ActiveSheet.Name = Right(c.Value, 30)
Next c

End Sub

By the way, I don't know what the ActiveSheet.Name = Right(c.Value, 30) line does, but I put it in since it was in the code I was modifying. I'd appreciate it if someone could help me figure out what's going wrong with my code.

Thanks a bunch.

 

[Momentman]

if the sheet created has a name of right(c.value,30) then to delete it, the name of the sheet must be right(c.value,30), 'cos it wont find a sheet named c.value

 

[snipescc]

Wait, so does that mean the line is there to make sure the names are exactly right?

The creation part of the code does work. And the delete part starts to work but stops. It deletes the sheet with a name from cell b2, but not b3, b4, b5 ect.

 

[Momentman]

Why do you have this line in your delete macro

ActiveSheet.Name = Right(c.Value, 30)

?

 

[snipescc]

It was in the code I got for the first macro. Since the deleting macro that it was based on the creating macro, I though putting that code in there might help make it work. One of my questions is what exactly does that code do in either one.

 

[Momentman]

One of my questions is what exactly does that code do in either one

"c" in your code represents a range but more specifically, a cell i.e B2, B3, B4,...... what that line does is to look into for example B2, then picks 30 characters from the right of whatever is contained in cell B2 and names the new sheet that value, let me say for a change, you used right(c.value,5) and what is contained in B2 is JackSpark, the code would extract 5 characters from the right, which would be "Spark" and it would name the sheet being added as "Spark", I hope that makes some sense, The code keeps looping from B2, B3 and so on and performing the same operation

 

[snipescc]

That's quite helpful. I just didn't know where the number 30 came from, and what the direction meant. Do you know why when I run the delete marco, it will delete the sheet named after the first cell, but not keep looping back?

Thank you very much.

 

[Momentman]

For me, maybe dis is more like it,

Sub trial()
    For Each c In Sheets("FilterList").Range("b2:b74")
        Application.DisplayAlerts = False
        Worksheets(Right(c.Value, 30)).Delete
    Next c
End Sub[/code[

 

[Momentman]

For me, maybe dis is more like it,

Sub trial()
    For Each c In Sheets("FilterList").Range("b2:b74")
        Application.DisplayAlerts = False
        Worksheets(Right(c.Value, 30)).Delete
    Next c
End Sub

 

[snipescc]

Hmm, that did the same thing. It deleted the first sheet, but not the ones after that. I'm really kind of stumped about why this isn't starting at the beginning like it is supposed to.