Multiple Copy/Paste operations causing issues

Jakson

New Member
Joined
Sep 14, 2021
Messages
12
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I think this is a common(ish) problem. I'm running an AHK macro that copies a series of contiguous and non contiguous cells. The data copied gets loaded into some variables and later pasted into a webform. This works fine - once. I can copy and paste all of my data in one go successfully once before issues arise. Specifically I first experience major slow down.

I can watch the AHK macro visually move my mouse across the spreadsheet, select cells, but Excel is too busy working on updating the visual appearance of the workbook and going from cut copy to edit mode. After about half of a second, my AHK macro rapidly outpaces Excel. If I allow my macro to continue to run indefinitely, as it is set up to do, it will cause Excel to slow down further and further until visual elements on the workbook stop being updated at all. The window buttons, the minimize, maximize, and close buttons, all remain working regardless of how visually "broken" Excel becomes.

I can interact with control buttons on the workbook as though nothing is wrong. When a "screenupdate" procedure is run by VBA it unbreaks some of the visual updates. The spreadsheets and cells begin to update visually again but menu options and buttons remain visually broken.
For clarity, by "broken" I mean that all visual updates have stopped at all. When I select a cell no highlight surrounds the cell, when copying no dashes appear, when highlighting a range, no flashing highlight appears. When interacting with control buttons on the workbook they do not visually appear to be clicked, instead, the actions from the VBA operation simply happen. When clicking on ribbon buttons nothing changes visually. Whatever was displayed remains unchanged.

I'm thinking this is a byproduct of my macro's interaction speed with Excel. While developing other AHK macros for Excel in the past, I noticed that if I executed too many copy/paste commands too near to each other I would experience data loss or incomplete actions. Some copies would not fire at all even if the cell was selected, or an entire range of cells would be selected where only one should have been. Additionally, if a copy happens near enough to a paste operation neither will happen or only one will happen.

In other forums I've browsed for guidance some users mentioned that widespread use of formatting and conditional formatting may cause some of this slow down when performing normal-human-speed copy/paste operations. My workbook is stuffed with formulas and formatting. Specifically of the 8 cells I am copying with my macro, 5 of them will be raw text and the other 3 will be formula values. All 8 cells are on the same row, however, the rows must be filtered conditionally and remain filtered throughout the operation of the macro.

Any guidance is deeply appreciated.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jakson

New Member
Joined
Sep 14, 2021
Messages
12
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I think this is a common(ish) problem. I'm running an AHK macro that copies a series of contiguous and non contiguous cells. The data copied gets loaded into some variables and later pasted into a webform. This works fine - once. I can copy and paste all of my data in one go successfully once before issues arise. Specifically I first experience major slow down.

I can watch the AHK macro visually move my mouse across the spreadsheet, select cells, but Excel is too busy working on updating the visual appearance of the workbook and going from cut copy to edit mode. After about half of a second, my AHK macro rapidly outpaces Excel. If I allow my macro to continue to run indefinitely, as it is set up to do, it will cause Excel to slow down further and further until visual elements on the workbook stop being updated at all. The window buttons, the minimize, maximize, and close buttons, all remain working regardless of how visually "broken" Excel becomes.

I can interact with control buttons on the workbook as though nothing is wrong. When a "screenupdate" procedure is run by VBA it unbreaks some of the visual updates. The spreadsheets and cells begin to update visually again but menu options and buttons remain visually broken.
For clarity, by "broken" I mean that all visual updates have stopped at all. When I select a cell no highlight surrounds the cell, when copying no dashes appear, when highlighting a range, no flashing highlight appears. When interacting with control buttons on the workbook they do not visually appear to be clicked, instead, the actions from the VBA operation simply happen. When clicking on ribbon buttons nothing changes visually. Whatever was displayed remains unchanged.

I'm thinking this is a byproduct of my macro's interaction speed with Excel. While developing other AHK macros for Excel in the past, I noticed that if I executed too many copy/paste commands too near to each other I would experience data loss or incomplete actions. Some copies would not fire at all even if the cell was selected, or an entire range of cells would be selected where only one should have been. Additionally, if a copy happens near enough to a paste operation neither will happen or only one will happen.

In other forums I've browsed for guidance some users mentioned that widespread use of formatting and conditional formatting may cause some of this slow down when performing normal-human-speed copy/paste operations. My workbook is stuffed with formulas and formatting. Specifically of the 8 cells I am copying with my macro, 5 of them will be raw text and the other 3 will be formula values. All 8 cells are on the same row, however, the rows must be filtered conditionally and remain filtered throughout the operation of the macro.

Any guidance is deeply appreciated.
**********Edit:

I found a user on a different forum site saying that excessive sheet calculations might have to do with the root of the problem. They recommended turning off automatic formula calculations and I did such. My problem has gone away for now after turning sheet calculations to manual.

If I continue to experience the issue mentioned in the OP I'll make another edit.

Resolution:
Navigate to the Formulas tab in your ribbon and click the "Calculation Options" button. Change formula updates from Automatic to Manual.
 

Jakson

New Member
Joined
Sep 14, 2021
Messages
12
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
**********Edit:

I found a user on a different forum site saying that excessive sheet calculations might have to do with the root of the problem. They recommended turning off automatic formula calculations and I did such. My problem has gone away for now after turning sheet calculations to manual.

If I continue to experience the issue mentioned in the OP I'll make another edit.

Resolution:
Navigate to the Formulas tab in your ribbon and click the "Calculation Options" button. Change formula updates from Automatic to Manual.
******************Edit 2:

Turning Sheet Calculations from Automatic to Manual only partially resolved the issue I was experiencing. When running my AHK macro with Sheet Calculations set to Manual I don't experience any slow down anymore. Excel executes the copy/pastes more efficiently now, strangely. However, when returning to a sheet with macro control buttons on it I found that they had become visually broken and were not appearing as they should. There is no VBA code causing the buttons to disappear, they simply were not visually present. Upon further investigation I found that the buttons were still present and behaving as expected besides being invisible. Interacting with the buttons while invisible did not cause them to suddenly become visible again, even when one of the VBA macros has a "ScreenUpdate" command line setting the value to True again.

I'm stumped all over again.
 

Forum statistics

Threads
1,143,840
Messages
5,721,101
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top