0

I am trying to paste info on the last row of a different worksheet. I already have the info copied and this macro is just to paste it. With the code that I have now, I am getting an error that says "Paste Method of Worksheet Class Failed" how can I fix this?

here is the code:

Windows("m.xlsx").Activate
Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Range("D45").Select
Windows("d.xlsx").Activate
Community
  • 1
  • 1
acabr136
  • 1
  • 1
  • try to fully qualify your `Cells` object, you are missing the `Worksheets` object. For example `Workbooks("m.xlsx").Worksheets("SheetName").Cells(...).Copy` – Shai Rado Jun 06 '17 at 13:43

1 Answers1

0

Excel is known to clear the clipboard and hence you should put the copy command one line before you paste.

So in your case the copy command will go before

ActiveSheet.Paste

Also please avoid the use of .Select/Activate. Please read How to avoid using Select in Excel VBA macros

Is this what you are trying (Untested)?

Sub Sample()
    Dim thisWb As Workbook, thatWb As Workbook
    Dim lRow As Long

    Set thisWb = Workbooks("d.xlsx")
    Set thatWb = Workbooks("m.xlsx")

    '~~> Change Sheet1 to the relevant sheet
    With thatWb.Sheets("Sheet1")
        '~~> Find last Row to paste
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        '~~> Change Sheet1 to the relevant sheet
        '~~> Replace A1:A10 with the relevant range that you are trying to copy
        thisWb.Sheets("Sheet1").Range("A1:A10").Copy .Range("A" & lRow)
    End With
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks for your response, what I'm trying to do is sort of like that but since I am inexperienced with VBA, I was combining this macro with a previous macro that ended with copying a group of filtered data. My ultimate goal is to copy from "d" to the last row of "m" – acabr136 Jun 06 '17 at 14:02
  • Copy what from "D" ? – Siddharth Rout Jun 06 '17 at 14:07
  • Some filtered data, the code for that looks like this: `ActiveSheet.Range("$J$12:$O$64").AutoFilter Field:=4, Criteria1:="No" ActiveWindow.SmallScroll Down:=-21 Rows("16:16").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy` – acabr136 Jun 06 '17 at 14:09
  • What is the logic? Why are you going to 16th row and copying from that row? – Siddharth Rout Jun 06 '17 at 14:15
  • Sorry that was a mistake. That should be: `Cells.Select` – acabr136 Jun 06 '17 at 14:18
  • `Sorry that was a mistake. That should be: Cells.Select – acabr136 23 secs ago` Then why the autofilter? What exactly are you trying to copy? Can you give us a little more detail? – Siddharth Rout Jun 06 '17 at 14:19