Excel Macro works in 2000 but not in 2003 - HELP!
 

All,
I have a Macro that throws error 1004 on the ShowAllData method. This
Macro will work on 2000 but not 2003. Below is the code. Any help
would be greatly appreciated. I copied the start of the macro down to
the error part. Does this have anything to do with ActiveSheet???

Sub Macro_Filter()

' Beginning here updates the BP340.RES sheet for BP340/RES

Dim foundcell As Range
Application.DisplayAlerts = False
Sheets("BP340.RES").Activate
ActiveSheet.UsedRange.Select
FirstRow = Selection.Rows.Count
SELECTROWS = "A3:N" & FirstRow
Range(SELECTROWS).Select
Selection.Delete

Sheets("FY").Select
If ActiveSheet.Protect Then
ActiveSheet.Unprotect
End If

Range("A1").Select

On Error GoTo ErrHndlr1
Selection.AutoFilter Field:=7, Criteria1:="BP340"
Selection.AutoFilter Field:=8, Criteria1:="RES"
ActiveSheet.UsedRange.Select
LastRow = Selection.Rows.Count
Selection.Range("A2:N" & LastRow).Select
Selection.Copy
Sheets("BP340.RES").Activate
Range("A3").Select
ActiveSheet.Paste

ErrHndlr1:
Sheets("BP340.RES").Activate
Columns("G").Find("", after:=[G2]).Select
Set CurrentSelection = Application.ActiveCell
CurrentSelection.Value = "Grand Total:"
With ActiveCell
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 10
End With
Selection.EntireRow.Insert
Set CurrentSelection = Application.ActiveCell
CurrentSelection.Value = "BP340 Total:"
With ActiveCell
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 10
End With
Selection.EntireRow.Insert
K = 3
Do Until Range("G" & K).Value = ""
Range("G" & K).Select
K = K + 1
Loop
mystr = Trim(Str(K))
Range("J" & mystr + 1).Formula = "=sum(J3:J" & mystr & ")"
TotSpent1 = Range("J" & mystr + 1).Value
Range("J" & mystr + 2).Formula = "=sum(J2:J" & mystr & ")"
Sheets("FY").Select
Range("A1").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If

Excel Macro works in 2000 but not in 2003 - HELP!
 

Hi Walt -

Nothing jumps out as being the obvious problem and as the code relies on a
lot of code and sheet configuration we don't have, it's a bit tough to throw
it into excel to debug it.

As a general point, Activesheet, Activecell etc are generally not a good
thing to use within your code as if the selection changes (a user clicks for
example) halfway through your code then it could make things go very wrong...



" wrote:

All,
I have a Macro that throws error 1004 on the ShowAllData method. This
Macro will work on 2000 but not 2003. Below is the code. Any help
would be greatly appreciated. I copied the start of the macro down to
the error part. Does this have anything to do with ActiveSheet???

Sub Macro_Filter()

' Beginning here updates the BP340.RES sheet for BP340/RES

Dim foundcell As Range
Application.DisplayAlerts = False
Sheets("BP340.RES").Activate
ActiveSheet.UsedRange.Select
FirstRow = Selection.Rows.Count
SELECTROWS = "A3:N" & FirstRow
Range(SELECTROWS).Select
Selection.Delete

Sheets("FY").Select
If ActiveSheet.Protect Then
ActiveSheet.Unprotect
End If

Range("A1").Select

On Error GoTo ErrHndlr1
Selection.AutoFilter Field:=7, Criteria1:="BP340"
Selection.AutoFilter Field:=8, Criteria1:="RES"
ActiveSheet.UsedRange.Select
LastRow = Selection.Rows.Count
Selection.Range("A2:N" & LastRow).Select
Selection.Copy
Sheets("BP340.RES").Activate
Range("A3").Select
ActiveSheet.Paste

ErrHndlr1:
Sheets("BP340.RES").Activate
Columns("G").Find("", after:=[G2]).Select
Set CurrentSelection = Application.ActiveCell
CurrentSelection.Value = "Grand Total:"
With ActiveCell
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 10
End With
Selection.EntireRow.Insert
Set CurrentSelection = Application.ActiveCell
CurrentSelection.Value = "BP340 Total:"
With ActiveCell
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 10
End With
Selection.EntireRow.Insert
K = 3
Do Until Range("G" & K).Value = ""
Range("G" & K).Select
K = K + 1
Loop
mystr = Trim(Str(K))
Range("J" & mystr + 1).Formula = "=sum(J3:J" & mystr & ")"
TotSpent1 = Range("J" & mystr + 1).Value
Range("J" & mystr + 2).Formula = "=sum(J2:J" & mystr & ")"
Sheets("FY").Select
Range("A1").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.ShowAllData
End If