I have a macro which was running from a long time. Suddenly it stopped working. Upon digging I found out that the macro is not running properly only when it is called from the button. If I try the macro through F8 or Developer Tab everything seems to work fine.
The exact issue is that when called from button the code which is supposed to copy an entire sheet from one workbook to another sheet of a different workbook is pasting the content from below cell instead of A1. As a result excel is not able to paste.
This is only happening when called from button.Has anyone faced this issue.
Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer
'Set system properties
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 error is happening at the last two lines.
Bookmarks