CODEWITHSUNDEEP
vbaexcel
99moorem
99moorem

Reputation: 1983

Method 'PasteSpecial' of object 'Range' failed

I've been looking at this for ages now and I know its something simple.

the code below is throwing me an error "Method 'PasteSpecial' of object 'Range' failed" is the error

With xlWB.Sheets("Lookups")
    'Copy lookups tab to word test tables
    .Cells.Copy
    ThisWorkbook.Sheets("Word_Test_Tables").Cells.PasteSpecial
    Application.CutCopyMode = False
End With

The error occurs on the pastespecial.

Once this error has happened the code will continue to f5 through but nothing will actually happen, I can not select any tabs or workbooks and have to end task the application.

Putting a break point on the error line I can get a range from 

?ThisWorkbook.Sheets("Word_Test_Tables").Cells

I have tried activating the sheet, unhiding the sheet. I can confirm xlWB is valid and open, .cells.copy looks to be working as you get the highlighting round the sheet.

Any ideas?

Upvotes: 1

Views: 4223

Answers (3)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

You may have worksheets with different sizes depending on the source workbooks. Try copying a smaller Range.

Besides, you probably want ActiveWorkbook instead of ThisWorkbook.

Upvotes: 0

Rory
Rory

Reputation: 34045

I don't really know why you would copy all the cells rather than just the used range:

    xlWB.Sheets("Lookups").Usedrange.copy _
Destination:=ThisWorkbook.Sheets("Word_Test_Tables").Range("A1")

Upvotes: 1

Uberzen1
Uberzen1

Reputation: 415

You need to come out of your 'with' to paste the values in the other workbook:

With xlWB.Sheets("Lookups")
'Copy lookups tab to word test tables
.Cells.Copy

End With

ThisWorkbook.Sheets("Word_Test_Tables").Cells.PasteSpecial

Upvotes: 0

Related Questions