Nicolaf
02-07-2014, 06:56 AM
Hi,
I have a macro which works fine but after it has run, my Excel seems to freeze for short periods of time (two/three seconds) every now and again.
My intuition is that the macro has something missing or something is incorrect in code that leads to this annoying problem.
Can someone please have a quick look at code and let me know if there is an apparent improvement I can make?
The macro will open workbooks found in column B (of Sheet Final List) at sheet name found in column A (of Sheet Final List).
It will then take cell values in Cell N6 and O6 (of workbook opened) and paste them in Cells D and E (of Sheet Final List).
Macro code:
Private Sub CommandButton1_Click()
Dim List As Worksheet
Dim BkList As Range
Dim ShtList As Range
Dim FixList As Range
Dim lastrow1 As Long
Dim Iname As String
Dim SName As String
Dim NewWkBk As Workbook
Dim cel As Long
Set List = ThisWorkbook.Sheets("Final List")
With List
lastrow1 = .Range("B7").End(xlDown).Row
Set BkList = .Range("B7:B" & lastrow1)
Set ShtList = .Range("A7:A" & lastrow1)
Set FixList1 = .Range("D7:D" & lastrow1)
Set FixList2 = .Range("E7:E" & lastrow1)
End With
List.Calculate
On Error Resume Next
For cel = 1 To BkList.Count
Iname = BkList.Cells(cel).Value 'workbook to open
Set NewWkBk = Workbooks.Open(Filename:="P:\Local " & Iname)
SName = ShtList.Cells(cel).Value 'sheet name to open
Workbooks(Iname).Sheets(SName).Calculate
FixList1.Cells(cel).Value = Workbooks(Iname).Sheets(SName).Range("N6").Value
FixList2.Cells(cel).Value = Workbooks(Iname).Sheets(SName).Range("O6").Value
Workbooks(Iname).Sheets(SName).Calculate
Workbooks(Iname).Saved = True
Workbooks(Iname).Close
Set NewWkBk = Nothing
celnext:
Next cel
End Sub
The problem (freezing) appears when I click my left mouse button to go from one cell to another or to go from one sheet to another.
What is strange is that even if I close the workbook completely and work on another workbook (same Excel session) the problem is still there.
The only way to get rid of it is to close Excel and start a new Excel session.
Any help/suggestion is appreciated.
Thanks,
Nix
:think: :doh: :think:
I have a macro which works fine but after it has run, my Excel seems to freeze for short periods of time (two/three seconds) every now and again.
My intuition is that the macro has something missing or something is incorrect in code that leads to this annoying problem.
Can someone please have a quick look at code and let me know if there is an apparent improvement I can make?
The macro will open workbooks found in column B (of Sheet Final List) at sheet name found in column A (of Sheet Final List).
It will then take cell values in Cell N6 and O6 (of workbook opened) and paste them in Cells D and E (of Sheet Final List).
Macro code:
Private Sub CommandButton1_Click()
Dim List As Worksheet
Dim BkList As Range
Dim ShtList As Range
Dim FixList As Range
Dim lastrow1 As Long
Dim Iname As String
Dim SName As String
Dim NewWkBk As Workbook
Dim cel As Long
Set List = ThisWorkbook.Sheets("Final List")
With List
lastrow1 = .Range("B7").End(xlDown).Row
Set BkList = .Range("B7:B" & lastrow1)
Set ShtList = .Range("A7:A" & lastrow1)
Set FixList1 = .Range("D7:D" & lastrow1)
Set FixList2 = .Range("E7:E" & lastrow1)
End With
List.Calculate
On Error Resume Next
For cel = 1 To BkList.Count
Iname = BkList.Cells(cel).Value 'workbook to open
Set NewWkBk = Workbooks.Open(Filename:="P:\Local " & Iname)
SName = ShtList.Cells(cel).Value 'sheet name to open
Workbooks(Iname).Sheets(SName).Calculate
FixList1.Cells(cel).Value = Workbooks(Iname).Sheets(SName).Range("N6").Value
FixList2.Cells(cel).Value = Workbooks(Iname).Sheets(SName).Range("O6").Value
Workbooks(Iname).Sheets(SName).Calculate
Workbooks(Iname).Saved = True
Workbooks(Iname).Close
Set NewWkBk = Nothing
celnext:
Next cel
End Sub
The problem (freezing) appears when I click my left mouse button to go from one cell to another or to go from one sheet to another.
What is strange is that even if I close the workbook completely and work on another workbook (same Excel session) the problem is still there.
The only way to get rid of it is to close Excel and start a new Excel session.
Any help/suggestion is appreciated.
Thanks,
Nix
:think: :doh: :think: