Excel Aborts in copy/paste macro


Posted by Doug Kelner on October 25, 1999 9:47 AM

I've been having Office97 aborts when running a macro
which does copy/paste of large amounts of data. Microsoft
engineers said that cause is clipboard not clearing
memory. Fix was to rewrite macros using technology other
than copy/paste. P.s., adding memory was a suggestion
which didn't work. Below is a sample macro:

Call input_wks(destfilename, "idpmanex", "a1", "q:\finance\idp\idpmanex.xls", "idpmanex")

Sub input_wks(destbook, destsheet, destrange, sourcefile, sourcesheet)
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet
Dim ws2 As Worksheet, rng1 As Range, rng2 As Range
Set wb1 = Workbooks(destbook)
Set ws1 = wb1.Worksheets(destsheet)
Set rng1 = ws1.Range(destrange)
rng1.Value = " "
Workbooks.Open FileName:=sourcefile
Set ws2 = ActiveSheet
Set wb2 = ActiveWorkbook
ws2.UsedRange.Select
Set rng2 = ws2.UsedRange
Set rng1 = rng1.Resize(rng2.Rows.Count, rng2.Columns.Count)
rng1.Value = rng2.Value
wb2.Close False
End Sub



Posted by Jamie on October 26, 1999 9:06 AM

This is a shot in the dark, try adding
Application.CutCopyMode = false
in your code, I believe this may dump what was last copied from memory
which in theory might free some up so place it after you have pasted and before
you copy again.

The help for this tells a simpler story, but I seem to remember this speeding things up in the past