Okay so I have three things I need help with.
1.) I have excel 2007 and when I recorded the macro yesterday it worked just fine, but today it's coming up with the box to update values. The macro is set to open up the vendor assignment sheet and do a vlookup against the clerk and then return the information to the original sheet and then copy paste special values. 2.) Today it's also doing the calculating thing in the corner using 2 processors which it has not done before. 3.) Run-time error '1004': PasteSpecial method of Range class failed.
1.) I have excel 2007 and when I recorded the macro yesterday it worked just fine, but today it's coming up with the box to update values. The macro is set to open up the vendor assignment sheet and do a vlookup against the clerk and then return the information to the original sheet and then copy paste special values. 2.) Today it's also doing the calculating thing in the corner using 2 processors which it has not done before. 3.) Run-time error '1004': PasteSpecial method of Range class failed.
Code:
' Keyboard Shortcut: Ctrl+r
'
Dim OriginalSheet As Workbook
Set OriginalSheet = ActiveWorkbook
Columns("B:B").Cut
With Columns("A:A")
.Insert Shift:=xlToRight
End With
With Columns("B:B")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Range("B1") = "VENDOR NAME"
Range("C1") = "VENDOR#"
Range("D1") = "PO3"
Range("E1") = "ATR3"
Range("F1") = "LOC#"
Range("G1") = "QTY"
Range("H1") = "AMOUNT"
Range("I1") = "REC'D DATE"
With Columns("E:J")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Range("E1") = "INVOICE#"
Range("F1") = "AS400 LOC"
Range("G1") = "INV ERROR"
Range("H1") = "EDI VENDOR"
Range("I1") = "PO COST DIFF"
Range("J1") = "CB RELATED"
With Columns("P:P")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Range("P1") = "DAYS OLD"
With Columns("R:R")
.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Range("R1") = "REQUEST INVOICE"
Columns("T:T").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("T1") = "DAY RANGE"
With Range("O:O,S:S")
.NumberFormat = "m/d/yyyy"
End With
Columns("O:O").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=((TODAY()+(CHOOSE(WEEKDAY((TODAY())),0,1,2,3,4,5,6)))-45)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.499984740745262
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("S:S").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=TODAY()+5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.499984740745262
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("T:T").ColumnWidth = 13.57
Columns("P:P").ColumnWidth = 10.14
ActiveCell.FormulaR1C1 = "DUEDATE"
Range("P2") = "=TODAY()-RC[-1]"
With Range("P2")
.NumberFormat = "General"
.AutoFill Destination:=Range("P2:P5000")
End With
With Range("P2:P5000")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]>90,""OVER 90"",IF(RC[-4]>=60, ""60 TO 90"",IF(RC[-4]>=45,""45 TO 59"",IF(RC[-4]>=30, ""30 TO 44"",""UNDER 30""))))"
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T5000")
Range("T2:T5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ChDir "S:\Merchandise AP New\Vendor Assignment"
Application.DisplayAlerts = True
OriginalSheet.Activate
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(C[1],'[Vendor Assignments - List for Processors.xlsx]ASSIGNMENTS'!C1:C2,2,FALSE)"
Selection.AutoFill Destination:=Range("B2:B5000")
With Range("B2:B5000")
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Range("A1").Select
Application.CutCopyMode = False
End Sub[code]
