FredMFoley
Board Regular
- Joined
- Mar 18, 2002
- Messages
- 58
When I run my code, it copies a large selection of cells (about 220 columns by 2200 rows) in order to move them one column to the right.
It then enters new data into column 30.
Range(Cells(1, 30), Cells(ActiveSheet.UsedRange.Rows.Count, 252)).Select
Selection.Copy
Cells(1,30).Select
ActiveSheet.Paste
I run into this problem when I have more than one set of data to enter.
When I open the workbook and run the code it runs normally. It takes about 1min 23 sec to complete.
However if I re-run it (exactly the same code) to get the next set of data, without closing and re-opening the workbook, then the VBA "hangs" for about 70 seconds on the line Selection.Copy
It takes about 2min 35sec to complete the second (and subsequent) sets of data.
The code ruins at normal speed before and after that line.
During the time it is "hanging", the Windows Task Manager shows Excel is "not responding". (Before and after the "copying" it says it is running.)
Something is snagging on that Selection.Copy line which won't clear unless the workbook is closed and re-opened. (I know it hangs exactly on that line by stepping through the code)
Someting gets cleared or reset by re-opening the workbook.
Simply saving the workbook between sets of data doesn't "fix" it.
Any help or suggestions would be very welcome.
Thank You
Fred
Other information:
Windows XP Pro
Microsoft Excel 2003
workbook is large (15MB) with a large VBA program
It then enters new data into column 30.
Range(Cells(1, 30), Cells(ActiveSheet.UsedRange.Rows.Count, 252)).Select
Selection.Copy
Cells(1,30).Select
ActiveSheet.Paste
I run into this problem when I have more than one set of data to enter.
When I open the workbook and run the code it runs normally. It takes about 1min 23 sec to complete.
However if I re-run it (exactly the same code) to get the next set of data, without closing and re-opening the workbook, then the VBA "hangs" for about 70 seconds on the line Selection.Copy
It takes about 2min 35sec to complete the second (and subsequent) sets of data.
The code ruins at normal speed before and after that line.
During the time it is "hanging", the Windows Task Manager shows Excel is "not responding". (Before and after the "copying" it says it is running.)
Something is snagging on that Selection.Copy line which won't clear unless the workbook is closed and re-opened. (I know it hangs exactly on that line by stepping through the code)
Someting gets cleared or reset by re-opening the workbook.
Simply saving the workbook between sets of data doesn't "fix" it.
Any help or suggestions would be very welcome.
Thank You
Fred
Other information:
Windows XP Pro
Microsoft Excel 2003
workbook is large (15MB) with a large VBA program