Pastespecial error on first run

Question

This sub is set up to copy info over from one worksheet and paste the values into a new CSV workbook. I keep getting a runtime error on the pastespecial, however, it's only on the first click after opening the spreadsheet, if I click it again it works perfectly. And even though it gives me an error, when i click end it still pastes the values over.

Sub export_save()

Dim nrows As Integer
Dim norders As Integer
Dim i As String
Dim cell As Range
Dim fname As String
Dim WS As Worksheet
Dim WK As Workbook
Set WK = Workbooks.Add
Dim k As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
k = 2
i = "DO" 'plant to plant movement


'name new file
On Error GoTo canceled
fname = InputBox("Please name the new file, exlude any filename   extensions.", "Export Data")

WK.SaveAs Filename:="S:\Active Customers\Teknor Apex\Feeds\Orders\" & fname, _
    FileFormat:=xlCSV
    MsgBox ("File saved to file path:S:\Active Customers\Teknor  Apex\Feeds\dev\" & fname)

'copy info over
Workbooks("Teknor Template dev").Worksheets("REFORMATTED").Activate
nrows = Rows(Rows.Count).End(xlUp).Row
Workbooks("Teknor Template dev").Worksheets("REFORMATTED").Range("A3:AG" & nrows).copy
WK.Activate
Range("A1:AG" & nrows).PasteSpecial xlPasteValues, Operation:=xlNone,  SkipBlanks _
    :=False, Transpose:=False


'remove parentheses
norders = Rows(Rows.Count).End(xlUp).Row
Range("AI2").FormulaR1C1 = "=MID(RC[-14],FIND(""("",RC[-14],1)+1,3)"
Range("AI2").AutoFill Destination:=Range("AI2:AI" & norders),  Type:=xlFillDefault
Range("AI2:AI" & norders).copy
Range("U2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,  SkipBlanks:=False, Transpose:=False
Columns("AI:AI").Delete Shift:=xlToLeft

'remove ship paratheses in DO orders
For Each cell In Range("B2:B" & norders)
    If cell.Value = i Then
        Range("AI" & k).FormulaR1C1 = "=MID(RC[-13],FIND("" ("",RC[-13],1)+1,3)"
        Range("AI" & k).copy
        Range("V" & k).PasteSpecial xlPasteValues, Operation:=xlNone,  SkipBlanks:=False, Transpose:=False
    End If
    k = k + 1
Next cell

'delete extra column used to remove paratheses
Columns("AI:AI").Delete Shift:=xlToLeft

WK.Save
Application.ScreenUpdating = True
Application.DisplayAlerts = True

canceled:

End Sub

For clarity's sake here is a smaller version containing only the error, which is in the pastespecial line.

Workbooks("Teknor Template dev").Worksheets("REFORMATTED").Activate
nrows = Rows(Rows.Count).End(xlUp).Row
Workbooks("Teknor Template dev").Worksheets("REFORMATTED").Range("A3:AG" & nrows).copy
WK.Activate
Range("A1:AG" & nrows).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

TheGuyThatDoesn'tKnowMuch TheGuyThatDoesn'tKnowMuch · Accepted Answer · 2016-04-26T14:49:11

Change:

Range("A1:AG" & nrows).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

To:

Range("A1:AG" & nrows).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Your code is missing Paste:=

Pastespecial error on first run

1 Answers