PasteSpecial of Range class failed

Question

I am trying to select copy everything from a "database" workbook. and paste it in the current workbook, sheet 5. The code I am using is the following.

Sub Import()

Dim DBaseWB As Workbook
Set DBaseWB = Workbooks.Open("http://collaboration.pwc.ca/team/Plant5EngineLines/Documents/Plant 5 master build plan/Database.xlsm", UpdateLinks:=False)
' set DBaseWB as the database workbook after opening it from sharepoint.

Dim DBaseSheet As Worksheet
Set DBaseSheet = DBaseWB.Sheets(1)        'DBaseSheet is referenced to sheet 1 of Database workbook.

Sheet5.Cells.Clear
DBaseSheet.UsedRange.Copy                               'Copy everything from the database
Sheet5.Range("A1").PasteSpecial xlPasteAll              'Paste everything in sheet 5 of current workbook

Application.DisplayAlerts = False
DBaseWB.Close saveChanges:=False
Application.DisplayAlerts = True

End Sub

I am receiving an error when running the following code. Though, sometimes it doesn't give me an error.

Run-time error '1004': PasteSpecial method of Range class failed

I think I know why it's giving me an error at the PasteSpecial line. When I have a different cell selected in sheet 5, it gives me no error. but when I recheck, without selecting any other cell, (so it will have the pasted range selected), I get this error.

I tried using the following line between copy and pastespecial,

Sheet5.range("A1").select

it gives me the same error.

----------UPDATE----------

I first used DisplayName's solution and it worked until yesterday. But this morning, it was causing problems. It another error. Then I tried all other solutions with no luck. all gave me the same errors. I also added the workbook.worksheet to the solutions below giving me no luck. This time the error was with the copy method. I also noticed that lots of columns say #REF.

Versions I tried:

DisplayName's solution combined with thisworkbook.worksheet

Sheet5.UsedRange.Clear
With Workbooks.Open("http://collaboration.pwc.ca/team/Plant5EngineLines/Documents/Plant 5 master build plan/Database.xlsm", UpdateLinks:=False) 'open and reference your Database workbook
    .Sheets(1).UsedRange.Copy Destination:=ThisWorkbook.Sheets(5).Range("A1") ' copy referenced workbook sheet 1 content and paste it to sheet 5
    .Close False
End With

Gary's Student's solution combined with thisworkbook.worksheet

Dim DBaseWB As Workbook
Set DBaseWB = Workbooks.Open("http://collaboration.pwc.ca/team/Plant5EngineLines/Documents/Plant 5 master build plan/Database.xlsm", UpdateLinks:=False)
' set DBaseWB as the database workbook after opening it from sharepoint.

Dim DBaseSheet As Worksheet
Set DBaseSheet = DBaseWB.Sheets(1)        'DBaseSheet is referenced to sheet 1 of Database workbook.

Dim Destination As Worksheet
Set DestinSh = ThisWorkbook.Sheets(5)

Sheet5.Cells.Clear
DBaseSheet.UsedRange.Copy DestinSh.Range("A1").PasteSpecial     'copy database info in planning tool.

Application.DisplayAlerts = False
DBaseWB.Close saveChanges:=False
Application.DisplayAlerts = True

errors:

Run-time error '1004': Copy method of Range class failed

Answer 1

First insure that there are no merged cells on either worksheet, and then try:

DBaseSheet.UsedRange.Copy Sheet5.Range("A1")

Answer 2

you could simply go:

Sub Import()   
    Sheet5.UsedRange.Clear
    With Workbooks.Open("http://collaboration.pwc.ca/team/Plant5EngineLines/Documents/Plant 5 master build plan/Database.xlsm", UpdateLinks:=False) 'open and reference your Database workbook
        .Sheets(1).UsedRange.Copy Destination:=Sheet5.Range("A1") ' copy referenced workbook sheet 1 content and paste it to sheet 5
        .Close False
    End With
End Sub

Answer 3

I am with Bruce Wayne on this one

As you have opened a new workbook the focus is now on that workbook.

If you are trying to clear the cells in the workbook the code resides in (destination) after you have launched a new workbook and switched the focus you will need to reference the original workbook to do so, like this:

ThisWorkbook.Sheets(5).Cells.Clear

Hope this helps