You don't have to save the workbook for the code to work ok when you run it.

But there could be differences between worksheets--like you found with the
autofilter (on or off) or worksheet protection or ranges not the same (maybe
..Range("A7:Z7") doesn't have data???).

I don't have a guess without knowing what lines of code are broken and what the
data for that offending worksheet looks like.

wayne wrote:

On 07/15/2009 06:49 PM, Dave Peterson wrote:
I don't see anything in your code that refers to the name or codename.

If that code is in a General module, then both refer to the activesheet.

So you could use something like:

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With worksheets("nameyouseeinthetabinexcel")
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
End With

'or using the codename

With TheCodeNameYouSeeInTheVBE
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
End With

End Sub

=========
But I'm confused with your comment about the codename being "sheet(1)" or
"tablle(1)". Those parens aren't allowed in the codename property.

I'm betting that those are the names you see in the tabs in excel.

Then that With statement would look like:
with worksheets("Sheet1(1)")
or
with worksheets("tablle(1)")

Be careful with your spelling--it has to match exactly. Even space characters
are important!


My bad with the paren's. The sheets are named sheet1 etc. and tablle1 etc.

I spent a couple of days trying to get the code to work in one
spreadsheet, and then copied the modul1 from one workbook to another,
only to have failures. I am now mostly trying to learn why toing the
above does not seem to be possible. Everything looks the same in the
different workbooks excpt for the amount of data. Even the sheetnames
are the same, but when I get the code working in ine workbook, I ended
up with errors in the others, or no action by the script at all.
Stepping through the code looks like the steps are being read, but no
activity in the sheet.

Could I have to save the workbook after changing the VBA before running
it? It doesn't seem to matter while developing the code (I think).

Here is the complete module. Some of the VBA is not used, perhaps from
another project.

' Makro Auto Open runs every time the worksheet is opened
'

Sub Auto_Open()

'Log_Activity ("opened")

Filter_refresh
Rows("8:9999").Select
Selection.EntireRow.Hidden = False
Range("A8").Select

End Sub

<--not used
Sub Log_Activity(Action)

Application.ScreenUpdating = False
Sheets("Logsheet").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Now()
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Action
Sheets("To-Do's").Select
Application.ScreenUpdating = True

End Sub
<--end not used--

Sub sort_deadlinedate()
'
' sort_deadline date Macro
' Macro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
Rows("7:9999").Select
Selection.Sort Key1:=Range("H7"), Order1:=xlAscending,
Key2:=Range("F7") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom
Range("H8").Select
End Sub

Sub Filter_activate()

'
' Filter_activate Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A8").Select
End With

End Sub

Sub Filter_critical()
'
' Filter_critical Makro
' Macro modified by Wayne D Hammond 7/13/2009

With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A7:Z7").AutoFilter Field:=3, Criteria1:="x"
.Range("J8").Select
End With

End Sub
Sub Filter_refresh()
'
' Filter_refresh Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With ActiveSheet
.AutoFilterMode = False
End With

FreezePanes = False
Rows("7:7").Select
FreezePanes = True
Range("A8").Select

' Application.Run "Filter_notfinished"
' On Error Resume Next
' ActiveSheet.ShowAllData
' On Error GoTo 0
' ActiveWindow.FreezePanes = False
' Range("A8").Select
' ActiveWindow.FreezePanes = True
End Sub

Sub Filter_notfinished()
'
' Filter_notfinished Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:=""
.Range("J8").Select
End With

End Sub

Sub sort_number()
'
With ActiveSheet
.AutoFilterMode = False
End With
Rows("7:3000").Select
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, Key2:=Range("D7") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

Range("A8").Select
End Sub
Sub sort_inputdate()
'
' sort_inputdate Makro
' Makro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
'
Rows("7:3000").Select
Selection.Sort Key1:=Range("B7"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, Key2:=Range("D7") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1,
MatchCase:=False _
, Orientation:=xlTopToBottom

Range("A8").Select
End Sub

Sub sort_completiondate()
'
' sort_completion date Makro
' Macro am 24.05.2006 von Kerstin Kämper aufgezeichnet
'
'
With ActiveSheet
.AutoFilterMode = False
.Range("A7:Z7").AutoFilter
.Range("A7:Z7").AutoFilter Field:=10, Criteria1:="<"
.Range("J8").Select
End With
End Sub

The original vba did not check to see if the autofilter was active
before invoking it, just invoked it which seemed to toggle autofilter
instead of turning it on or off according to the module (filter_refresh
is supposed to turn off and filter_activate is supposed to turn on).

Regards,

--
Wayne
glenmeadows.us
A democracy is nothing more than mob rule, where fifty-one percent of
the people may take away the rights of the other forty-nine. [Thomas
Jefferson]

--

Dave Peterson