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
First insure that there are no merged cells on either worksheet, and then try:
DBaseSheet.UsedRange.Copy Sheet5.Range("A1")