Macro that ran in Excel 2010 VBA doesn't work in 2000 VBA.

[billfinn]

Good morning,
I need to make several macros created on Excel 2010 VBA run on Excel 2000 and so far it's been a real clinic. The macros run perfectly on Excel 2010.
I got one working after a while, but this next has me stymied. It's pretty much over my head. I commented out the error handler so I could track down the error.

The macro errors on the line;
Windows("Estimator.xlsm").Activate

I tried replacing Windows with Workbooks but got the same error.

If I comment that line out as a test
it next complains about the line;
Range("A:D").Replace Ary(i), "=xxx", xlWhole, , False, , False, FalseI will be extremely grateful for any suggestion or input
Thanks much!
Bill

Sub Strip_Page_Report_Headers()    'Macro to strip out Report and Page headings to leave an Excel File with columns properly formatted.
'Timer - comment out the three lines below if you don't want the timer to run
Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer


    On Error GoTo ErrorHandler
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .PrintCommunication = False
End With


    Windows("Estimator.xlsm").Activate  'Go to the Estimating Tool
    Sheets("Item Master").Select      'Select the Test Data worksheet in the Estimating tool
    Selection.EntireColumn.Hidden = False
    Dim Ary As Variant
    Dim i As Long
    Ary = Array("*QUERY*", "*INVMASTB*", "*INVMASTAAA*", "*LIBRARY*", "*PRICEMSM*", "*DATE*", "*REPORT", "*invmast*", "Item", "Prod", "Pricing", "*Cost*", "*:*", "*DELETED*", "*EDIORGI*", "*DELETE*", "*FORMAT*")
    For i = 0 To UBound(Ary)
    Range("A:D").Replace Ary(i), "=xxx", xlWhole, , False, , False, False
    Next i


    For i = 1 To 15
    On Error Resume Next
    Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
    On Error GoTo 0
Next


'Add Column Labels
Rows(1).Insert Shift:=xlDown
    Range("A1").FormulaR1C1 = "ITEM"
    Range("B1").FormulaR1C1 = "DESCRIPTION"
    Range("C1").FormulaR1C1 = "COST"


  ' Set Column C as currency
Worksheets("Item Master").Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"


Columns.AutoFit
  
ErrorHandler:
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .PrintCommunication = True
End With


'Timer end code - comment out two lines below if you don't want the timer to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
  MsgBox "This code ran successfully in " & MinutesElapsed, vbInformation




End Sub

 

[billfinn]

Disregard previous message, I neglected to click only on errors. Here is the code from only errors

Sub Macro4()'
' Macro4 Macro
' Macro recorded 9/4/2018 by user
'


'
    Range("A1").Select
    Selection.SpecialCells(xlCellTypeFormulas, 16).Select
End Sub

 

[Fluff]

In that case I don't understand why you got an error with

Columns(i).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete

 

[billfinn]

Fluff,
I truly appreciate your help. I would still be ripping my hair out without your assistance. Just for fun Excel crashed on this laptop I'm using and corrupted my workbook. I dug through backups and found one that was only about 3 hours old. I incorporated the changes from your suggestions into the backup copy and it seems to have cured the Special Cells error message for some reason. Probably I typed something correct this time that I typed wrong last time. Who knows? Now I get a Run Time error message that says No Cells Were Found. Not sure why, they certainly are there. Do I need to change "Columns(i).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete" to a different value than 16?
Thanks much!
Bill

 

[billfinn]

Finally got it. Changed the section of code that deletes lines to the following;

For i = 1 To 15    On Error Resume Next
    Columns(i).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    On Error GoTo 0
    Next

Everything working ok now. Again Fluff, thanks so much for your help!

 

[Fluff]

What code are you using?

 

[billfinn]

Just crashed again, I'll post the code after recovery

 

[billfinn]

Fluff,
Here is the final version of the code. This works just as it should.
Again, many thanks for your help!
Bill

Sub Strip_Page_Report_Headers()    'Macro to strip out Report and Page headings to leave an Excel File with columns properly formatted.'Timer - comment out the three lines below if you don't want the timer to run
Dim StartTime As Double
Dim MinutesElapsed As String
'Remember time when macro starts
StartTime = Timer




    'On Error GoTo ErrorHandler
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With




    Workbooks("Estimator.xls").Activate  'Go to the Estimating Tool
    Sheets("Item Master").Select      'Select the Item Master worksheet in the Estimating tool
    Selection.EntireColumn.Hidden = False
    Dim Ary As Variant
    Dim i As Long
    Ary = Array("*QUERY*", "*NVMAST*", "*info*", "*E N D*", "*LIBRARY*", "*PRICEMSM*", "DATE*", "*TIME*", "*PAGE*", "Item", "*Cost*", "*DELETE*")
    For i = 0 To UBound(Ary)
    Range("A:H").Replace What:=Ary(i), Replacement:="=xxx", LookAt:=xlWhole, MatchCase:=False
    Next i
    
    For i = 1 To 15
    On Error Resume Next
    Columns(i).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
    On Error GoTo 0
    Next


'Add Column Labels
Rows(1).Insert Shift:=xlDown
    Range("A1").FormulaR1C1 = "ITEM"
    Range("B1").FormulaR1C1 = "DESCRIPTION"
    Range("C1").FormulaR1C1 = "COST"




  ' Set Column C as currency
Worksheets("Item Master").Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"




Columns.AutoFit
  
'ErrorHandler:




With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With




'Timer end code - comment out two lines below if you don't want the timer to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
  MsgBox "This code ran successfully in " & MinutesElapsed, vbInformation








End Sub

 

[Fluff]

Glad it's sorted & thanks for the feedback