Paste of Range object is failing

[Tomcoll67]

Team,
I have tried debugging the below code and cannot find any issue, but it keeps failing with a 1004 error: Paste method of worksheet class failed.
The code works until it gets to the "ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("B2:B500") ' works with column B" line. Then fails.Thank you for any help.


Option Explicit
Dim fullPath As String

Private Sub FileOpenDialogBox()


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
.Show

fullPath = .SelectedItems.Item(1) 'Set file paths for the SN report and the current Yearbook version
End With
End Sub

Private Sub GetSNReport() ' Open The SN ID Report and Copy Column A then close the report
Dim erow

FileOpenDialogBox

Do While Len(fullPath) > 0
If fullPath = "Weekly comparison.xlsm" Then
Exit Sub
End If

'Open file once found
Workbooks.Open (fullPath)
Sheets("Page 1").Select
Range("A3:A501").Copy
ActiveWorkbook.Save
ActiveWorkbook.Close

'Workbooks("Weekly comparison.xlsm").Activate
'Worksheets("Sheet1").Activate
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("B2:B500") ' works with column B

fullPath = fullPath

Loop

fullPath = fullPath
MsgBox ("GetSNReport complete")

End Sub

 

[JoeMo]

That's a strange syntax to use for a paste. Try:

ActiveSheet.Range("B2").Paste

Or more simply just:

Range("B2").Paste

since your code has already made the desired destination sheet the Activesheet.

 

[Tomcoll67]

That's a strange syntax to use for a paste. Try:

ActiveSheet.Range("B2").Paste

Or more simply just:

Range("B2").Paste

since your code has already made the desired destination sheet the Activesheet.

When I try "Range("B2:B500").Paste"(""excluded) I get the Run-time error '438': Object doesn't support this property or method.
Thank you for assisting.

 

[JoeMo]

When I try "Range("B2:B500").Paste"(""excluded) I get the Run-time error '438': Object doesn't support this property or method.
Thank you for assisting.

You don't need to specify the entire range, just the first cell of same. Just try what I posted.

 

[Tomcoll67]

I tried with the first cell also and get the "paste method of worksheet class failed" message.

 

[JoeMo]

I tried with the first cell also and get the "paste method of worksheet class failed" message.

Looking at your code, it appears that you cleared the clipboard of the copy when you saved the source workbook after you copied. Change the sequence in your code so you do the paste immediately after the copy.

 

[Tomcoll67]

Looking at your code, it appears that you cleared the clipboard of the copy when you saved the source workbook after you copied. Change the sequence in your code so you do the paste immediately after the copy.

Thank you very much. It is now working.

 

[JoeMo]

Thank you very much. It is now working.

You are welcome - thanks for the reply.

 

[Tomcoll67]

One last question. How do I mark this as solved?

 

[JoeMo]

One last question. How do I mark this as solved?

Your post #7 is sufficient. We don't have a formal mechanism to mark threads as solved because that might discourage others from posting better solutions that we can all benefit and learn from.