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
Code:
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