Unable to paste copied cells when the sheet is hidden.

[kornie_jon]

Hello,
I have written some VBA code to copy cells from a "hidden" Sheet 3 when I click a "Form Controls" button on Sheet 2. Sheet 3 remains hidden, the information copies to the clipboard and I am able to past the information into Notepad or a blank Excel Workbook as a test. The problem is when I attempt to paste the information into a field within SharePoint, it will only paste if Sheet 3 is un-hidden when attempting to paste. Any ideas of what I can do to make this work?


[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Complete_Orders_button()

Sheet3.Range("Complete_Orders").Copy

End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[TheRobRush]

turn application screen updating off, unhide the sheet, copy paste, hide the sheet, turn application screen updating back on.

 

[kornie_jon]

Thank you for the reply. That screen updating off code helped with another project, thank you...

For this issue - it's the copy to paste step. Because I am pasting into a SharePoint outside of the Excel workbook, the macro moves past and hides the sheet before anything is pasted. This results in the same issue, the information is on the clipboard and I can paste it into a blank Excel sheet or onto Notepad but it will not past into SharePoint. Is there a code to hold, recognize that the information has been pasted and then continue the macro?

 

[TheRobRush]

it's actually simpler than you realize. had a similar issue recently where I needed a macro to not continue. make your sub, unhide, copy, paste, (create a secondary sub and CALL it after the paste that hides things back to how you want them), end sub let me know if that works, don't have sharepoint to test it out but it should fix the issue.