why does the box for update values come up when it's not in the code to come up?

liferg

Board Regular
Joined
May 21, 2013
Messages
88
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.

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]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,352
Enclose your macro with

[c o d e]
. . .
. . .
[/ c o d e]
highlights (remove the spaces)

You have

[c o d e]
. . .
. . .
[c o d e]
 

liferg

Board Regular
Joined
May 21, 2013
Messages
88
Sorry.

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
 

Forum statistics

Threads
1,136,954
Messages
5,678,758
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top