Converting Code from 2007 to 2003

[jemaxwell]

I created this code in Excel 2007 now i want to put it on my other users machines they are using Excel 2003 and i keep getting debug errors can someone help

Sub setup()
' Keyboard Shortcut: Ctrl+Shift+C
'add sheet
Sheets.Add After:=Sheets(Sheets.Count)
'format headers
    With Range("A1")
        .Value = "Job Name"
        .Name = "Arial"
    End With
    With Range("A2")
        .Value = "Quote #"
        .Name = "Arial"
    End With
    With Range("A3")
        .Value = "Job #"
        .Name = "Arial"
        
    End With
    With Range("b3")
        .Value = "DON'T FORGET THE JOB NUMBER"
        .Name = "Arial"
    End With
    
    Sheets("sheet2").Range("B2").Copy
    Range("B3").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
        Range("H8:Q8, D6:G6").MergeCells = False
        Range("H8").Copy Sheets("Sheet2").Range("B1")
    With Sheets("sheet1").Range("D6")
        .Font.Underline = xlUnderlineStyleNone
        .Copy Sheets("Sheet2").Range("B2")
    End With
    
'delete labor
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
    If Left(Cells(i, "B").Value, 10) = "APPR LOCAL" Then
        Rows(i).EntireRow.delete
    End If
    If Left(Cells(i, "B").Value, 2) = "JM" Then
        Rows(i).EntireRow.delete
    End If
    If Left(Cells(i, "B").Value, 7) = "MILEAGE" Then
        Rows(i).EntireRow.delete
    End If
    If Left(Cells(i, "B").Value, 10) = "EXCAVATION" Then
        Rows(i).EntireRow.delete
    End If
Next i

'copy material
 Sheets("sheet1").Range("B13:C120").MergeCells = False
 Sheets("sheet1").Range("j13:j120").Copy Range("c13")
 Sheets("sheet1").Range("b13:c120").Copy Sheets("Sheet2").Range("A5")
    ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Add Key:=Range("A5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet2").sort
        .SetRange Range("A5:B120")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("sheet1").Range("C13:C120").ClearContents
'Column Heads
    Sheets("sheet1").Range("M13:P97").MergeCells = False
   
    Sheets("sheet1").Range("M13:M97").Copy Sheets("sheet2").Range("L5")
    Sheets("Sheet2").Select
    
    
    Sheets("Sheet2").sort.SortFields.Clear
    Sheets("Sheet2").sort.SortFields.Add Key:=Range("L5"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With Sheets("Sheet2").sort
        .SetRange Range("L5:L89")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    With Range("$L$5:$L$89")
        .RemoveDuplicates Columns:=1, Header:=xlNo
    End With
    With Range("l5:L89").Copy
        Range("b4").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    End With
    Range("j4").Font.Bold = True
    With Range("j4")
        .Value = "Total"
        .HorizontalAlignment = xlCenter
    End With
Range("L5:L89").ClearContents
    
'column width
Columns("A:J").ColumnWidth = 10
'freeze pane
Rows("5:5").Select
        ActiveWindow.FreezePanes = True
'add formulas
Range("J5").FormulaR1C1 = "=SUM(RC[-8]:RC[-1])"
    
Range("J5").Copy Range("J6:J49")
'info
    With Range("i50")
        .Value = "Prepared by:"
        .Name = "Arial"
    End With
   
    With Range("I51")
        .Value = "Checked by:"
        .Name = "Arial"
    End With
    
    With Range("I52")
        .Value = "Date:"
        .Name = "Arial"
    End With
    Range("J50").Select
'print setup
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.View = xlNormalView
End Sub

 

[Simon Lloyd]

Where is it you have the problem? if you meant you are trying to use the file you created in 2007 on a machine that has 2003 then you need to save your file down to 97-2003 version

 

[RoryA]

You need to rewrite the Sort parts as the Worksheet.Sort property does not exist prior to 2007. The rest looks OK from a quick glance.

 

[Norie]

Jenn

Any code that is going to be used on different versions of Excel should be developed in the earliest version.

By the way why do you have this.

Sheets.Add After:=Sheets(Sheets.Count)
'format headers
    With Range("A1")
        .Value = "Job Name"
        .Name = "Arial"
    End With
    With Range("A2")
        .Value = "Quote #"
        .Name = "Arial"
    End With
    With Range("A3")
        .Value = "Job #"
        .Name = "Arial"
 
    End With
    With Range("b3")
        .Value = "DON'T FORGET THE JOB NUMBER"
        .Name = "Arial"
    End With

It could be replaced with this.

    Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
 
    With wsNew.Range("A1:A3")
           .Value = Application.Transpose(Array("Job Name", "Quote #", "Job #"))
           .Font.Name = "Arial"
    End With
    With wsNew.Range("b3")
        .Value = "DON'T FORGET THE JOB NUMBER"
        .Font.Name = "Arial"
    End With

PS Why does the comment say it format's the headers?

 

[jemaxwell]

how do i re-write it, everything i try still gives me errors.

 

[erik.van.geit]

you should tell where you have the errors

 

[jemaxwell]

Run-time error '438':

Object doesn't support this property or method

It gives this error at the

'copy material
 Sheets("sheet1").Range("B13:C120").MergeCells = False
 Sheets("sheet1").Range("j13:j120").Copy Range("c13")
 Sheets("sheet1").Range("b13:c120").Copy Sheets("Sheet2").Range("A5")
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A5:B120")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("sheet1").Range("C13:C120").ClearContents

 

[erik.van.geit]

what line is highlighted (yellow) when you get the error

 

[jemaxwell]

'copy material
 Sheets("sheet1").Range("B13:C120").MergeCells = False
 Sheets("sheet1").Range("j13:j120").Copy Range("c13")
 Sheets("sheet1").Range("b13:c120").Copy Sheets("Sheet2").Range("A5")
    [I][B]ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
[/B][/I]    [I][B]ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range[/B][/I]("A5"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
   [B][I] With ActiveWorkbook.Worksheets("Sheet2").Sort
[/I][/B]        .SetRange Range("A5:B120")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("sheet1").Range("C13:C120").ClearContents

i bolded where it stops

 

[Norie]

jen

Perhaps if you explained in words what the code is actually meant to do we could help further.

Obviously you are creating a new sheet and populating some cells on it.

But then things become a bit hazy, for me anyway.

The main reason for that is unqualified references like this.

Range("H8:Q8, D6:G6").MergeCells = False

Without a reference to a worksheet VBA will be looking at the range on what it considers the active sheet.

Which may or may not be the one you wish to refer to.