VBA: Paste method of range class failed

Question

I am trying to copy some filtered data from a work sheet and paste it to a new csv file that I create within the coding. I get the error "Paste method of range class failed". any ideas?

Sub Button1_Click()
Dim wkb As Excel.Workbook
Dim wkb2 As Excel.Workbook
Dim answer As Integer
Dim crtra1 As String
Dim crtra2 As String
Dim path
UserForm1.Show
path = ThisWorkbook.path & "\" & fldr & "\BookHierarchy.csv"
     Set wkb = Application.Workbooks.Open(path)
     crtra1 = "TOTUS"
     crtra2 = "US"
     wkb.Sheets(1).Range("A1").Resize(Rows.Count, Columns.Count).AutoFilter Field:=13, Criteria1:="=*" & crtra1 & "*"
     wkb.Sheets(1).Range("A1").Resize(Rows.Count, Columns.Count).AutoFilter Field:=14, Criteria1:="=" & crtra2
     wkb.Sheets(1).Range("A1").Resize(Rows.Count, Columns.Count).Select

     Selection.Copy
     Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.path & "\TOTUS_Books_US_Mapped.csv"
    path = ThisWorkbook.path & "\TOTUS_Books_US_Mapped.csv"
    Set wkb2 = Application.Workbooks.Open(path)
     'ActiveWorkbook.Sheets(1).Select
     'ActiveWorkbook.Sheets(1).Range("A1").Select
    wkb2.Sheets(1).Range("A1").PasteSpecial
     Application.CutCopyMode = False
      wkb.Sheets(1).AutoFilterMode = False
End Sub

You also need to qualify what sheet you want `Rows.Count` and `Columns.Count` to occur on. See how you did that with `Range()`? You need to add the workbook and worksheet to `Rows.Count` and `Columns.Count` too. Finally, instead of using `Selection.Copy`, just do `wkb.Sheets(1).Range("A1").Resize(Rows.Count, Columns.Count).Copy`. Also, perhaps add the `Copy` part *after* saving the workbook, to prevent any issues with the clipboard messing up. Finally, do you want just the values of the copied data? — BruceWayne, Feb 02 '17 at 17:11
You are doing a lot of operations between the Copy and Paste functions, which probably causes excel to "lose" the CutCopyMode. I suggest you prepare the new sheet first and then do the Copy-Paste immediately after each other. — PSotor, Feb 02 '17 at 17:47
[Possibly relevant](http://stackoverflow.com/q/41981099/1188513) — Mathieu Guindon, Feb 02 '17 at 18:15

score 0 · Answer 1 · answered Feb 02 '17 at 18:47

I solved the problem with this code:

Sub Button1_Click()
    Dim wkb As Excel.Workbook
    Dim wkb2 As Excel.Workbook
    Dim answer As Integer
    Dim crtra1 As String
    Dim crtra2 As String
    Dim path
    Dim rw As Integer
    Dim clm As Integer
    UserForm1.Show
    path = ThisWorkbook.path & "\" & fldr & "\BookHierarchy.csv"
     Set wkb = Application.Workbooks.Open(path)
     crtra1 = "TOTUS"
     crtra2 = "US"
     wkb.Sheets(1).Range("A1").Resize(Rows.Count, Columns.Count).AutoFilter Field:=13, Criteria1:="=*" & crtra1 & "*"
     wkb.Sheets(1).Range("A1").Resize(Rows.Count, Columns.Count).AutoFilter Field:=14, Criteria1:="=" & crtra2

     rw = wkb.Sheets(1).UsedRange.Rows.Count
     clm = wkb.Sheets(1).UsedRange.Columns.Count

     Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.path & "\TOTUS_Books_US_Mapped.csv"
    path = ThisWorkbook.path & "\TOTUS_Books_US_Mapped.csv"
    Set wkb2 = Application.Workbooks.Open(path)
     wkb.Sheets(1).Range("A1").Resize(rw, clm).Copy

    wkb2.Sheets(1).Paste
     Application.CutCopyMode = False
      wkb.Sheets(1).AutoFilterMode = False
End Sub

VBA: Paste method of range class failed

1 Answers1