Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xls file works fine in Excel 2000 and 2007 but crashes on opening in 2003 | Excel Discussion (Misc queries) | |||
Object works in Excel 2000 but not in 2003 | Excel Programming | |||
#VALUE! error: vlookup works in Excel 2000 but not 2003 | Excel Discussion (Misc queries) | |||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000 | Excel Programming | |||
VBA Code works in Excel 2003 but won't work in Excel 2000 | Excel Programming |