Call Stack failing after 2-3 macros.

[pleepleus]

Hey all, New here and hoping someone here has had a similar problem, I cannot find any mention of this anywhere else and when google fails me I get nervous.

I've put together a series of macros that complete a particularly undesirable task (previously my bosses task) relatively quickly. Unfortunately he learned about this and now I have to make this process user friendly. Well, I figured what could be more friendly than a button? So I created another macro that would call the others one by one (not an unheard of practice from what I can gather. It goes a little like...

Sub CoupDeGras2()  
Call CleanSheets 
Call RenameSheets 
Call Sort 
Sheets("1").Select 
Call NUMBER 
Call Report 
End Sub

The Problem is that the stack fails after calling Rename Sheets, without so much as an error message to cling to. The odd part is if I comment out Rename Sheets and the first sheet select it will go all the way to number. In fact commenting out any of the items causes it to go one macro further. (it stands mentioning that RenameSheets has an internal call command to another macro I entitled IdiotTrap as a sort of shot at myself. I don't think it has any stake in this problem but its probably worth saying)

I'm fairly new to the whole macro world so there might be an answer so obvious no-one's actually committed it to print. Any help at all would be appreciated.

Thanks,
Pleepleus

 

[Ruddles]

How do you know the stack is failing if you're not getting an error message?

Is there any Worksheet_Change or Workbook_Calculate code, or any other code which is triggered by events like Worksheet_Activate or Worksheet_SelectionChange?

I suspect you will have to make the entire workbook available for us to look at.

 

[pleepleus]

Ruddles,

The results of the individual macros are fairly easy to gauge, if run unaltered it will delete the unused sheets ("cleansheets"), rename them based on company name and date of order but will leave them unsorted, unnumbered and will not report their values to the other workbook. Each individual macro runs perfectly, but when run in conjunction its almost as if I commented out the last few lines.

As soon as I get Back to Work Tomorrow I will post the original workbook for review.

Thanks for your Reply,
Pleepleus

 

[pleepleus]

Here are the macros from the worksheet, (the actual worksheet has too much confidential info to adequately edit and leave anything similar to the original.) They are an odd mix of code I got off of excel help sites, lines I pieced together from inference and eventually my own code once I started seeing the patterns. I'd list the authors if applicable where I borrowed code, but they were all anonymous and I've forgotten most of where I got it. Again, if your looking at this, thanks for taking the time. I realize I just posted about a hundred and fifty lines of mess.

Sub CleanSheets()
    Dim shtTemp As Worksheet
    
    Application.DisplayAlerts = False
    For Each shtTemp In ActiveWorkbook.Worksheets
        If shtTemp.Range("A17") = "" And shtTemp.Range("D17") = "" Then
            shtTemp.Delete
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Sub RenameSheets()

mycount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To mycount

Call IdiotTrap

Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
wks.Name = wks.Range("L13").Text & wks.Range("D5").Value
Next wks
If i = mycount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Sub IdiotTrap()

Dim sPODate As String
Dim sPrompt As String
Dim sTitle As String
Dim sDefault As String

sPrompt = "Please supply date for this sheet."
sTitle = "PO Date"
sDefault = "01-01-01"

If ActiveSheet.Range("L13") = "" Then
ActiveSheet.Range("L13") = InputBox(sPrompt, sTitle, sDefault)
End If

End Sub

Sub Sort()
Dim i As Integer
Dim j As Integer
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
Next j
Next i
End Sub

Sub NUMBER()
Dim lastnumber As Variant
mycount = Application.Sheets.Count
Sheets(1).Select
For i = 1 To mycount
Windows("Handwritten PO's.xlsx").Activate
lastnumber = Range("B65536").End(xlUp)
Windows("Verbal PO Redux.xlsm").Activate
Range("R2") = ActiveSheet.Index + lastnumber
If i = mycount Then
End
End If
ActiveSheet.Next.Select
Next i
Sheets(1).Select
Range("A17").Select
End Sub

Sub Report()

Dim valuefromthisworkbook As Variant
mycount = Application.Sheets.Count
For i = 1 To mycount


Sheets(i).Select
valuefromthisworkbook = ActiveSheet.Range("R2").Value
Windows("Handwritten PO's.xlsx").Activate
Range("B65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = valuefromthisworkbook
If valuefromthisworkbook = "" Then ActiveCell.Value = "-"

Windows("Verbal PO Redux.xlsm").Activate
Sheets(i).Select
valuefromthisworkbook = ActiveSheet.Range("L13").Value
Windows("Handwritten PO's.xlsx").Activate
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = valuefromthisworkbook
If valuefromthisworkbook = "" Then ActiveCell.Value = "-"

Windows("Verbal PO Redux.xlsm").Activate
Sheets(i).Select
valuefromthisworkbook = ActiveSheet.Range("D5").Value
Windows("Handwritten PO's.xlsx").Activate
Range("C65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = valuefromthisworkbook
If valuefromthisworkbook = "" Then ActiveCell.Value = "-"

Windows("Verbal PO Redux.xlsm").Activate
Sheets(i).Select
valuefromthisworkbook = ActiveSheet.Range("O17").Value
Windows("Handwritten PO's.xlsx").Activate
Range("E65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = valuefromthisworkbook
If valuefromthisworkbook = "" Then ActiveCell.Value = "-"
Windows("Verbal PO Redux.xlsm").Activate


If i = mycount Then
End
End If
ActiveSheet.Next.Select
Next i

End Sub

Pleepleus

 

[RoryA]

Remove any line that is just:

End

since it does exactly what it says...

 

[Ruddles]

Ah! You can't use End like that!

That will stop Visual Basic completely. If you just want to terminate execution of the subroutine and return to the calling program, you should use Exit Sub.

Edited: Grr, pipped again!

 

[pleepleus]

Exit Sub Worked Like a charm. Who'd have thought that the word End could actually have something to do with my macro ending prematurely.

Thanks for the reality check guys, Solved.