runtime error 1004 pastespecial method of range class failed

[forginganewone]

I keep getting this error "runtime error 1004 pastespecial method of range class failed" on following code :

Option Explicit

Sub copypasterow()
'
' chequerecieving Macro
' copy active row to another workbooks last row
'
' Keyboard Shortcut: Ctrl+l
'
Dim wbTarget As Workbook
Selection.EntireRow.Copy
Set wbTarget = Workbooks.Open("D:\Office\Rental Cheques\Recieving\Recieving File 1\Recieving file 1.xlsx")
wbTarget.Sheets("Sheet1").Range("C1").End(xlDown).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False

End Sub



Please help

 

[forginganewone]

I suppose you could have a shape or something selected which would produce an error but that would be an obvious mistake. But you need to do something like:

wbTarget.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1, -2).PasteSpecial xlPasteValues

Same error sir

I have tried the below totally different code aswell but it gives the same error.. its blowing my mind :

Option Explicit
Sub copypasterow()
'
' chequerecieving Macro
' copy active row to another workbooks last row
'
' Keyboard Shortcut: Ctrl+l
'

Dim wbTarget As Workbook
Dim Filetarget As String
Dim WorksheetEndRow As Integer


Filetarget = "D:\Office\Rental Cheques\Recieving\Recieving File 1\Recieving file 1.xlsx"


Selection.EntireRow.Copy
Set wbTarget = Workbooks.Open(Filetarget)
WorksheetEndRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A" & WorksheetEndRow + 1).PasteSpecial
Application.CutCopyMode = False

End Sub

 

[Dryver14]

Steve, your right, not sure what I was thinking...unless of course the desired cell had not been previously selected

 

[forginganewone]

Yes i know. But if you copy a whole row you have to paste it into column A. You cannot paste it into column C. It wont fit.

Tried that aswell .. could it be a problem with my excel or VBA ?
It can't be though as every other thing works perfectly even the power BI
T

 

[steve the fish]

Same error sir

I have tried the below totally different code aswell but it gives the same error.. its blowing my mind :

Option Explicit
Sub copypasterow()
'
' chequerecieving Macro
' copy active row to another workbooks last row
'
' Keyboard Shortcut: Ctrl+l
'

Dim wbTarget As Workbook
Dim Filetarget As String
Dim WorksheetEndRow As Integer


Filetarget = "D:\Office\Rental Cheques\Recieving\Recieving File 1\Recieving file 1.xlsx"


Selection.EntireRow.Copy
Set wbTarget = Workbooks.Open(Filetarget)
WorksheetEndRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A" & WorksheetEndRow + 1).PasteSpecial
Application.CutCopyMode = False

End Sub

You have missed out the sheet name now from the target workbook. What does WorksheetEndRow equal? Put a message box in there to find its value once its been assigned one.

 

[forginganewone]

You have missed out the sheet name now from the target workbook. What does WorksheetEndRow equal? Put a message box in there to find its value once its been assigned one.

I think the previous code i posted orinignally should work, please help fix that. If you can amend that code and fix errors and paste it again .. i will be really helpful.

 

[steve the fish]

Try replacing your paste special line from the original formula with my paste special line from post number 8. If that doesnt work. Do the steps manually. Are you able to paste? ie is the paste special greyed out.