Compile Error: Expected variable or procedure, not module.

[rcreek09]

This is the end of a module where I am calling (attempting to call) three other modules to finish up. Everything works fine except the sort worksheets line. If I take it out all is good. When I add it, the code won't even run. I get the expected procedure not module error. It's a module just like the AF, and Rowattop.


Application.ScreenUpdating = True

Call AutoFilterRequest

Call RowattopbyRequest

Call SortWorksheets

End Sub


Using XP, XL 2003. I can do it manually after the macro finishes, but would really like to understand what's happening and why.


Thanks very much for any help.


Deb

 

[Luke M]

Can you post the SortWorksheets macro? (or at least a sample of it)


Possible issues:

Macro is marked private and is in another module

Name isn't right (SortWorksheet instead of SortWorksheets)

Macro requires an argument

Is there some OnTime event in your coding?


Just kind of spit-balling some ideas here.

 

[rcreek09]

Certainly. Each of these are separate modules, all in one workbook.


Thanks!


Sub SortWorksheets()


Dim N As Integer

Dim M As Integer

Dim FirstWSToSort As Integer

Dim LastWSToSort As Integer

Dim SortDescending As Boolean

Application.ScreenUpdating = False

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then

'Change the 1 to the worksheet you want sorted first

FirstWSToSort = 1

LastWSToSort = Worksheets.Count

Else

With ActiveWindow.SelectedSheets

For N = 2 To .Count

If .Item(N - 1).Index <> .Item(N).Index - 1 Then

MsgBox "You cannot sort non-adjacent sheets"

Exit Sub

End If

Next N

FirstWSToSort = .Item(1).Index

LastWSToSort = .Item(.Count).Index

End With

End If

For M = FirstWSToSort To LastWSToSort

For N = M To LastWSToSort

If SortDescending = True Then

If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then

Worksheets(N).Move Before:=Worksheets(M)

End If

Else

If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then

Worksheets(N).Move Before:=Worksheets(M)

End If

End If

Next N

Next M

Application.ScreenUpdating = True

Sheets(1).Activate

End Sub

 

[Luke M]

Hmm. Everything looks good. BTW, nifty little macro. Mind if I copy it to my Personal file? I could use it on some of the tasks I work on.


Going into the realm of "this doesn't work even though it should":

If you change the order of which macro you call, does it work?

Can we change the nesting of macro? I.e., make a new small macro that does something like:

Sub MiniMacro

Call MainMacro

Call SortWorksheets

End Sub

 

[rcreek09]

It's a very nifty macro. Help yourself. It's not mine. I googled it long ago and got it from a forum somewhere. I usually try to note where it came from, but missed it on this one. I use it all the time. I did turn off the screen updating in it, but I think that's the only change I made to it.


I can't remember if I tried changing the order or not. Will try again. Also will try the minimacro idea. I have done that before and it work, but didn't think of that in this case.


Thanks so much for your prompt help.

Have a great day.

 

[vaskov17]

Might happen when the module and the sub inside have the same name

 

[rcreek09]

Just for the record, changing the order and calling the sort first does not work. Won't even work if I take the others out. Minimacro that calls the mainmacro, then the others did not work either. I can live with it, just thought it might be something obvious that I was overlooking.


This must be a 'cat' macro. Refuses to be called.


Thanks anyway. I feel better now.


Deb

 

[Luke M]

This must be a 'cat' macro. Refuses to be called.

Ha ha ha, must be. Sorry we couldn't help. :/