Need a little help with a For Each statement

[ribhead]

I'm just trying to keep my sheet numbers straight when I delet them with a macro. Can someone please tell me why this will not work? If I do 1 to n I get a subscript out of range.

Sub dude()
Application.DisplayAlerts = False
Dim mycount As Integer
Dim n As Integer
For Each ws In Sheets
n = n + 1
For mycount = n to 2
Sheets(mycount).Delete
Next mycount
Next ws
End Sub

I may not be very smart but I'm sure wirey!!!!

 

[Stretchwickster]

All you need is this:

Sub dude()
  Dim mycount As Integer
  Dim n As Integer
   
  Application.DisplayAlerts = False 
  For Each ws In Sheets
    ws.Delete
  Next ws
End Sub

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Stretchwickster]

Also, you have not initialised n to be any value. It's always good practice to do this. Another method (although not as efficient as the for...each) to achieve the same thing would be this:

Sub dude()
  Dim mycount As Integer

  Application.DisplayAlerts = False
  For mycount = 1 to Sheets.Count
    Sheets(mycount).Delete
  Next mycount
End Sub

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[TonyJollans]

Hi ribhead,

I was about to write a quick response and I went back and looked at your code. What are you trying to do?

1. Your outer For says do this once for each sheet.
2. You increment n continually without ever resetting it. As you have never initialised it, it will start out as zero. First time through it will become 1, second time 2, third time 3, etc.
3. You inner For is driven from n. First time it will be done twice, second time once, third and further times not at all (because n is greater than 2 and climbing).
4. Deleting sheets - First time through the outer loop you will delete sheet 1. What was sheet 2 will now become sheet 1, etc. Now you delete sheet 2 (which was sheet 3) so the old sheet 4, which had become sheet 3 now becomes sheet 2, etc. Second time through your outer loop you delete sheet 2 (which was originally sheet 4). Further times through you won't delete any sheets.
5. The overall effect is that, no matter how many sheets you have is that you delete sheets 1, 3 and 4 (unless of course you had less than that to start with)


My original quick response was going to be that when you have deleted a sheet all the others move down 1. So say you have 3 sheets and you delete sheet 1, you are left with sheets 1 and 2 (which used to be sheets 2 and 3). You increment your loop counter and delete sheet 2 (which was sheet 3). There are now no more sheets so when you increment your loop counter and try and delete sheet 3 you get 'subscript out of range'. The way round this is to work from the end backwards - make your loop control: For n = Sheets.Count to 1 Step -1, but note that you cannot delete ALL the sheets in a workbook - you must leave 1.


Hi Clive,

While I've been writing this epistle I see that you have posted twice (I must be getting slow at typing as well as thinking)

Both your code samples try and delete all sheets. The second also suffers from the same problem ribhead has and should get 'subscript out of range'.

Enjoy,
Tony

 

[ribhead]

Thank you Tony, what I am trying to do is create a command button for me and write this in my personal macro workbook. What I'm trying to do is set the number of sheets through an input box. Since I'm not that versed in VBA I thought I had to delete all but one ws and start adding them to whatever the input box is. Then I have to go back and rename the sheets starting from the end because if I don't I'll get an alarm because there will be two sheets with the same name. Let me take a breath. O.K. Does that explain it a bit better. I have to go chop the lawn now and hopefully one of my limbs.

I may not be very smart but I'm sure wirey!!!!

 

[ribhead]

I included Tony's point as well as an error handler.

Sub dude()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For n = Sheets.Count To 1 Step -1
On Error GoTo errclear
Sheets.Delete
Next n
errclear:
Err.Clear
End Sub

Don't know if this is the best way to go about doing this. What is the best way to add sheets using an input box? Any thoughts?

I tried doing the following

Sub dude()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
x = InputBox("How many ribhead?&quot
For n = Sheets.Count To 1 Step -1
On Error GoTo errclear
Sheets.Delete
Next n
errclear:
Err.Clear
ActiveWorkbook.Sheets.Add , "Sheet1", x
End Sub

But of course since I'm prorammming, it doesn't work. Any suggestions?



I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

Chopped my lawn yesterday - it's raining today and it'll grow again - some things you never get on top of.

Can we start from the top?

You want to set the number of sheets and are happy to delete (arbitrarily) those already there. Either you are in a new workbook or an old one you want to clear down. If it's an existing one you want to clear down do be aware, as I said before, that you must always have at least one sheet - if that's what you are doing come back; for now let's assume you are in a new workbook.

You have a number of sheets (you can change it but it defaults to 3) to start with. In code you can pick up this number from Sheets.Count. If you want, say, six then just add 3 more.

By default, all your sheets will be called "Sheet"number. If you want to rename them to similar names which might conflict then there are a couple of ways to proceed (look at reordering sheets - or using temporary names which don't conflict); again come back if you want to do this and get stuck. If you want to name them to names which will not conflict (e.g. ribhead1, ribhead2, etc.) then go right ahead and do it.

The only time you need to delete sheets is when you have more than you want and here a simple loop going backwards should do it ..

Application.DisplayAlerts = False
For mycount = Sheets.Count to

NumberWanted

 + 1
    Sheets(mycount).Delete
Next mycount

I think I'm asking more questions than I'm answering but please come back if I've got it all wrong - or even if I haven't and you need more help.

Enjoy,
Tony

 

[ribhead]

Thanks a lot for helpin me Tony. Yes I am opening a new workbook and I am going to create a new commandbar in the cells commandbars. I am a manufacturing supervisor and I track a lot of stuff by weeks. So, I like to change the sheets to weeks. I also want to able to set the number of weeks to whatever I need. Kind of get the conjunction junction what's my function? By the way

What is this supposed to do? if I use this after I .Add I put the sheet name and I select the After What is the After and count for in this string of code?

ActiveWorkbook.Sheets.Add , "Sheet1"Before,After,count,Type

It's something like that anyway.

I may not be very smart but I'm sure wirey!!!!

 

[ribhead]

I typed this in and nothing happened am I cursed?

Sub dude()
Dim numberwanted As Integer
Application.DisplayAlerts = False
numberwanted = InputBox("How many ribhead&quot
For mycount = Sheets.Count To numberwanted + 1
Sheets(mycount).Delete
Next mycount
End Sub

I may not be very smart but I'm sure wirey!!!!

 

[ribhead]

This is what I originall had.
Probably way too much code but I'm not a Mental Giant by any means. Any suggestions would be great.

Sub Sheets_to_weeks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim num_sheets As Integer
Dim count As Integer
num_sheets = Application.InputBox("Number of weeks you would like to add?" & vbCr & vbCr & "Note: You must enter a number" & vbCr & " greater than or equal to 2 ", "Week Entry Box", , , , , , 1)
If num_sheets <= 1 Then
Do
num_sheets = Application.InputBox(&quot;Number of weeks you would like to add?&quot; & vbCr & vbCr & &quot;Note: You must enter a number&quot; & vbCr & &quot; greater than or equal to 2 &quot;, &quot;Week Entry Box&quot;, , , , , , 1)
Loop Until num_sheets >= 2
Else
End If
For Each ws In Sheets
On Error GoTo errexit:
ws.Delete
Next ws
errexit:
Err.Clear
num_sheets = num_sheets - 1
Worksheets.Add , Sheets(1), num_sheets
rename_sheets
End Sub
Function rename_sheets()
Application.ScreenUpdating = False
For Each ws In Sheets
count = count + 1
ws.Name = &quot;Week&quot; & count
Next ws
End Function

I may not be very smart but I'm sure wirey!!!!

 

[ribhead]

This works for me. I guess I keep posting to my own thread. If someone has a lot faster way to do this I'd sure appreciate the tip.

Sub dude()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim mycount As Integer
Dim n As Integer
Dim newcount As Integer

n = InputBox(&quot;How many sheets?&quot

mycount = Sheets.Count
Line10:
If mycount <> 1 Then
Sheets(mycount).Delete
mycount = mycount - 1
GoTo Line10
Else
End If

If n = 1 Then
Exit Sub
Else
End If

Line20:
n = n - 1
Worksheets.Add
If n <> 1 Then
GoTo Line20
Else
End If

newcount = Sheets.Count
line30:
Sheets(newcount).Name = &quot;Week&quot; & newcount
newcount = newcount - 1
If newcount <> 0 Then
GoTo line30
Else
End If

I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

Seems like you've got there, but as you're always working on a new workbook, here's an alternative which uses Excel to do some of the dirty work, creating a new workbook with the right number of sheets to start with:

Sub MyNewWorkbook()

    Dim SheetsInNewWorkbook As Integer
    Dim SheetsWanted As Integer
    Dim SheetsPrompt As String
    Dim WeekNumber As Integer

    ' Prompt the User for a number of sheets
    ' This applies Excel max of 256 sheets - you might want to limit it further

    SheetsPrompt = "Number of weeks you would like to add?" & vbCr & vbCr & _
                   "Note: You must enter a number" & vbCr & _
                   "          greater than or equal to 2 "
    Do
        SheetsWanted = Application.InputBox(SheetsPrompt, _
                                            "Week Entry Box", , , , , , 1)
    Loop Until SheetsWanted > 1 And SheetsWanted <= 256

    ' Save the User's current default value
    ' Set no. of sheets to what we want and create a new workbook
    ' Restore the User's setting

    SheetsInNewWorkbook = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = SheetsWanted
    Workbooks.Add
    Application.SheetsInNewWorkbook = SheetsInNewWorkbook

    ' Rename All the sheets

    For WeekNumber = 1 To Sheets.Count
        Sheets(WeekNumber).Name = "Week" & WeekNumber
    Next

End Sub

Enjoy,
Tony

 

[ribhead]

Hey Tony that's great. I wasn't aware of the application.SheetsInNewWorkbook. It just seems like a pain that I have to change it in the ToolS>Option...blah blah then I have to open another workbook. A lot of stuff that's wasted time. Thanks for the help I really appreciate it.

By the way do Functions calculate faster than procedure or are they really only good for returning calculations and such to the main sub? I ask because If I only have one calculation to do would it be a good practice to use a Function or just a waste of time?

I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

Terminology can be very difficult in an environment such as this (where people of mixed ability, in both computers and English, communicate) but, unless I misunderstand, what you mean by a function is just another procedure.

Unless you're doing a lot of calculation you're not going to notice performance differences within your code and they shouldn't be a concern. Built-in functionality is almost always going to be quicker than your own code. Other than that, write your code so that you will be able to understand it when you revisit it in a year's time.

If you want any more on this, I suggest you start a new thread to better draw on the collective wisdom of the many fine contributors to the forum.

Enjoy,
Tony