Deleting sheets based on a range

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
125
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
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

Board Regular
Joined
Aug 29, 2010
Messages
125
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
Why do you have this line in your delete macro
Code:
ActiveSheet.Name = Right(c.Value, 30)
?
 

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
125
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
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
 
Last edited:

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
125
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
For me, maybe dis is more like it,
Code:
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

Well-known Member
Joined
Jan 11, 2012
Messages
3,963
For me, maybe dis is more like it,
Code:
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

Board Regular
Joined
Aug 29, 2010
Messages
125
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.
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top