Target Cell location issue while copying entire sheet by Macro

[AnimeshRoy]

I have a tool which multiple users have been running for quiet a long time. But last week suddenly few users started facing the issue with the error message "Run time error:1004 To paste all cells from an excel worksheet into the current worksheet, you must paste into the first cell (A1 or R1C1)"

So the piece of code that is doing the action is given below


Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = True
Range("CardiacLong_Contents").ClearContents
Application.ScreenUpdating = False

'Unprotect Process Sheet to insert important data
Sheets("Process").Select
ActiveSheet.Unprotect

Dim wb As Workbook
Dim wbOCLong As Workbook
Dim ws, wsp As Worksheet
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Cardiac Long")
Set wsp = wb.Worksheets("Process")
MsgBox "Open Cardiac OC Long Term Report"
Set wb = Workbooks.Open(Application.GetOpenFilename)
'Warning if there are more than one sheets
If (wb.Sheets.Count > 1) Then
MsgBox "This HINAI report has two sheets. Please merge them manually and then upload them"
End
End If
If (Columns.Count <= 256) Then
MsgBox "Please save the input or uploading files as *.xlsx or *.xlsb and then upload them"
End
End If

'Add the uploading file name as comment
wsp.Range("C6").ClearComments
wsp.Range("C6").AddComment wb.FullName

'Clearing the timestamp to point out the possible error
wsp.Range("TimeStamp_CardiacLong").ClearContents

wb.Worksheets(1).Range("A1:XFD1048576").Copy
ws.Range("A1").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False


The last two lines are copying from an entire sheet of a workbook and pasting into another sheet of a second workbook. The strange thing is although I am specifically written code to paste the data from cell A1, it is not doing so. It is trying to paste into the cell which was selected in the target sheet before running the macro. I couldn't replicate the issue in my system with the same office version although some of the users are constantly facing this.

Has anyone faced this issue earlier? Can you please help?

[Tinbendr]

I would say, this is your problem.

Please Login or Register  to view this content.

Now, wsp is pointing to the old file.

Try changing object name of the second WB to WB2 and corresponding code.