none
Running number of of copy and pasteformat operations in macro causes Windows Start menu to freeze RRS feed

  • Question

  • Other strange Excel UI issues start cropping up as well when doing a large number of copy paste functions. Forms will show up blank, missing UI elements, etc...

    Here is a stripped down version of a macro that exhibits the behavior on Windows 10 64 bit and Excel 2016 32 bit.

    Sub CopyPaste()

    Dim sFormatSheet As String
    Dim icurrentrow As Integer
    Dim i As Integer
    icurrentrow = 3

    sFormatSheet = "Sheet1"

    For i = 1 To 2000

    Me.Sheets(sFormatSheet).Rows(icurrentrow).Insert
    Me.Sheets(sFormatSheet).Range(Me.Sheets(sFormatSheet).Cells(2, 1), Me.Sheets(sFormatSheet).Cells(2, 13)).Copy Destination:=Me.Sheets(sFormatSheet).Range(Me.Sheets(sFormatSheet).Cells(icurrentrow, 1), Me.Sheets(sFormatSheet).Cells(icurrentrow, 13))

    Me.Sheets(sFormatSheet).Range(Me.Sheets(sFormatSheet).Cells(1, 1), Me.Sheets(sFormatSheet).Cells(1, 13)).Copy
    Me.Sheets(sFormatSheet).Range(Me.Sheets(sFormatSheet).Cells(icurrentrow, 1), Me.Sheets(sFormatSheet).Cells(icurrentrow, 13)).PasteSpecial xlPasteFormats
    Next i

    End Sub

    Thursday, May 12, 2016 11:29 PM

All replies

  • Hi Mark_Paul,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, May 13, 2016 2:07 AM
  • Hi Mark,

    >> Running number of of copy and pasteformat operations in macro causes Windows Start menu to freeze

    Do you mean you macro make your Windows start menu freeze? As far as I know, Me is a keyword in VB.net, how did you use Me in macro? If you manually copy and paste, will this issue happen?

    How many times you copy and paste will cause this issue? I suggest you delete the rows which copy and paste correct, and then check whether it is related with special rows.

    I suggest you use Application.Wait or DoEvents function.

    Best Regards,

    Edward

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 16, 2016 1:36 AM