I am getting Runtime error 1004" error message when you try to copy and to paste in Excel 2010

[Jpenn77]

Hi I am getting Runtime error 1004" error message when you try to copy and to paste in Excel 2010, when the macro tries to paste sheet 5 into sheet 6, here is the debug point where the code is failing the text is in orange. Can you help? I am trying to figure out why it is breaking at this point, Thank you

Public Sub PushToSheet(SheetPath As String, sheetname As String)


Dim WB_Source As Workbook
Dim WB_Destination As Workbook
Dim sh_source As Worksheet
Dim sh_destination As Worksheet
Dim currentSheetName As String
Dim wasvisible As Boolean
On Error GoTo 0




'' Set WB_SOURCE as the SOD_SOURCE workbook


Set WB_Source = Application.ActiveWorkbook
On Error Resume Next
'' set WB_DESTINATION as the sheet desired to output to
Set WB_Destination = Workbooks(sheetname)




'' If the destination sheet was not already open, this will open it, read-write, ignoring readonlyrecommended, updating links
If WB_Destination Is Nothing Then
Set WB_Destination = Workbooks.Open(SheetPath & sheetname, True, False, , , , True)
Else
End If


On Error GoTo 0


'' for each worksheet in the source sheet, check to see if the same sheet name exists in the destination sheet
For Each sh_source In WB_Source.Sheets
currentSheetName = sh_source.Name

Set sh_destination = Nothing
On Error Resume Next



Set sh_destination = WB_Destination.Sheets(currentSheetName)
On Error GoTo 0
If sh_destination Is Nothing Then ''if the destination sheet wasn't set in the previous line, it means there is no matching sheet in the destination workbook, so we dont have to copy the source sheet anywhere
GoTo ERROR_HANDLER ''this will skip to the next tab in the source sheet
Else
Application.DisplayAlerts = False
wasvisible = WB_Destination.Sheets(currentSheetName).Visible

''activates the source sheet, copys and pastes the values into the destination sheet.
WB_Source.Activate
WB_Source.Sheets(currentSheetName).Activate
ActiveSheet.Cells.Select
Application.CutCopyMode = False
Selection.Copy


sh_destination.Visible = True
sh_destination.Activate
ActiveSheet.Cells.Select
ActiveSheet.Paste


sh_destination.Visible = wasvisible ''if the sheet in the destination WB was originally visible, we'll set it that way again
Application.DisplayAlerts = True
End If




ERROR_HANDLER:


Next


If Not WB_Destination Is Nothing Then WB_Destination.Save
If Not WB_Destination Is Nothing Then WB_Destination.Close




Set WB_Source = Nothing
Set WB_Destination = Nothing
Set sh_source = Nothing
Set sh_destination = Nothing








End Sub

 

[Webbarr]

Try activating the cell you want to paste in, EG. If you want to paste in cell A1 then -

Range("A1").Activate
Activecell.PasteSpecial xl PasteValues

 

[Jpenn77]

Hello Webbarr

Thank you for the reply, I figured out what the cause of the error was. Under File | options | Trust Center | Trust Center Settings | External Content the Security settings for Data connections had to be changed from Prompt user about Data Connections to Enable all data Connections, I also under Security settings for Workbook links I had to change from Prompt user on automatic update for workbook links to Enable automatic update for all Workbook links. Once this was done the error went away and I had no issues.