CODEWITHSUNDEEP
excelvba
caro
caro

Reputation: 892

VBA paste method

I've been working on learning ways to better this code and taking examples from this site and others -- however I can't seem to get past the runtime error 1004 "paste method of worksheet class failed". I have two other similar macros and a button which will run all 3. It runs the first two with this same syntax around pasting into the "MyQueue" file just fine, but the third it will not paste and throws this error. Can anyone help? 

Sub CSQAgentSummaryEdit()

Dim MyPath As String
MyPath = " path "
MyFile = " file "
QueuePath = "path "
MyQueue = " file "
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = Workbooks.Open(QueuePath)
Set wb2 = Workbooks.Open(MyPath)

Columns("A:V").Delete Shift:=xlUp
Columns("B").Delete Shift:=xlUp
Columns("C:R").Delete Shift:=xlUp


 Range("A1").Select
  Selection.End(xlDown).Select
  ActiveCell.Offset(2, 0).Range("A1").Select
  Selection.consolidate Sources:= _
    "'file data " _
    , Function:=xlSum, LeftColumn:=True

    Range("A1").CurrentRegion.Delete Shift:=xlUp
    Rows("1:1").Delete

    Range("A1").CurrentRegion.Select
    With Selection.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

  Selection.Copy
  Workbooks.Open (QueuePath)
  Range("A1").Select
  Selection.End(xlDown).Select
  ActiveCell.Offset(20, 0).Range("A1").Select
  ActiveSheet.Paste , False
  Workbooks(MyQueue).Save
  Workbooks(MyFile).Close False

End Sub

Upvotes: 0

Views: 7466

Answers (3)

baldmosher
baldmosher

Reputation: 124

Remember that Excel is affected by external influences and by its own behaviour. VBA generally isn't. Active-anything is generally undesirable. Always better to:

set wsc = Sheets("Copy Sheet")
set wsp = Sheets("Paste Sheet")

..and then specify ranges within.

I also always use .PasteSpecial instead of .Paste -- it causes far fewer errors and is not reliant on activating the sheet, but that's actually the clue to your problem.

So, this part:

Selection.Copy
Workbooks.Open()  'I also think you did this twice so it could be removed, but see below
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(20, 0).Range("A1").Select
ActiveSheet.Paste , False

You don't need to select each cell, and as a minor point, .Cells(1) can be easier to read than .Range("A1") when specifying a single cell -- but both works. Just go for broke, and try this instead:

set rngToCopy = Selection
Set wsToPaste = wb2.Sheets("Name of Sheet")  'you don't need this if there's one sheet in the wb
rngToCopy.copy
wsToPaste.Cells(1).End(xlDown).Offset(20, 0).PasteSpecial
Application.CutCopyMode = False

Upvotes: 0

evoandy
evoandy

Reputation: 660

You seem to be opening the QueuePath workbook twice.

Instead of 

Selection.Copy
Workbooks.Open (QueuePath)
Range("A1").Select

Try 

Selection.Copy
wb2.Sheets("Sheet1").Range("A1").Select

Specifying which workbook you are using with will help avoid pasting to the wrong workbook/pasting errors.

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

Possibly the workbook you need to be active is not the right workbook. Check this link: Excel VBA pastelink run-time error 1004: Application-defined or object-defined error

Upvotes: 0

Related Questions