You could turn off your copy mode. Insert a line like below:

Next i
Application.CutCopyMode = False
Set prod = Nothing

End Sub

It might help much, but it does return focus to the range object.


"Li Jianyong" wrote:

Hello,

I have programmed a procedure to copy some ranges to new sheet. it can
finish the job. but the problem is after I finish this marco,and try to close
the datasource workbook. it can not close immediately,it keeps waiting. I
feel too much memory has been occupied. I try to copy in the sheet
manually,I found the last range ,I used to copy,is still im memory. How can
free up this memory? I suppose it can help me to close the workbook.

My macro as following:

Sub Generate_prod3()

Dim BOM As Worksheet

Dim Mingxi As Worksheet

Dim findrng As Range

Dim prod As Range

Dim i As Long, n As Integer

Const Col = 6


'Add new sheet as Mingxi

Sheets.Add after:=ActiveSheet

ActiveSheet.Name = "Mingxi"

Set Mingxi = Sheets("Mingxi")

Mingxi.Previous.Activate


Set BOM = Sheets("零件明细")

'copy the columnwidths to new sheet

BOM.Range("A1:F1").Copy

Mingxi.Range("A1").PasteSpecial xlPasteColumnWidths

BOM.Range("A1:F1").Copy Mingxi.Range("A1")


For i = 1 To Selection.Rows.Count

Set findrng = BOM.Cells.Find(what:=Selection.Cells(i, 1), lookat:=xlWhole)

n = findrng.End(xlDown).Row - findrng.Row

Set prod = findrng.Resize(n - 1, Col)

If i = 1 Then
prod.Copy Destination:=Mingxi.Range("A2")
Else

prod.Copy Destination:=Mingxi.Cells(Mingxi.UsedRange.Rows.Co unt + 2, 1)
End If
Next i

Set prod = Nothing


End Sub