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

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

thanks for your information. I have tried application.cutcopymode=false in my
Macro. but finally,I still have the problem. If I key Ctrl +C to the range of
the sheet, it still copied the last prod information. Is it problem from my
Variable defination?

Best regards
Li Jianyong

"JLGWhiz" wrote:

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

I don't believe the variable definition would affect the close process. When
the workbook closes it checks for things like a BeforeClose event procedure,
links to other workbooks, and any other clean up that has to be done to close
the file. It acually dumps the RAM, so having too much memory should not be
a problem. File size could cause it to be a little slow if you are into many
megabytes of file size. If the close event triggers calculations it could
delay the closing. There does not appear to be anything in the code posted
here that would cause a delayed closing process.

"Li Jianyong" wrote:

thanks for your information. I have tried application.cutcopymode=false in my
Macro. but finally,I still have the problem. If I key Ctrl +C to the range of
the sheet, it still copied the last prod information. Is it problem from my
Variable defination?

Best regards
Li Jianyong

"JLGWhiz" wrote:

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