K
Karen
Is there any way to modify the following code so that the
progress bar will update itself according to how far along
the macro is?
Sub Main()
Dim i As Long, tot As Long
tot = 100
progressDlg.Caption = "Prosessing data, please wait..."
For i = 1 To tot
If i Mod 5 = 0 Then ProgressBar i / tot
Application.ScreenUpdating = False
Sheets("RMA Log 2").Select
Application.Calculation = xlCalculationManual
Workbooks.Open Filename:="W:\BaanReports\Sales\rma
report.xls"
Cells.Select
Selection.Copy
Windows("RMA Reasons Report.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("rma report.xls").Activate
Application.CutCopyMode = False
ActiveWindow.Close
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Columns("D:F").Select
Range("D:F,H:H,I:I,J:J,K:K").Select
Range("K1").Activate
Range("D:F,H:H,I:I,J:J,K:K,L:L,M:M").Select
Range("M1").Activate
Range("D:F,H:H,I:I,J:J,K:K,L:L,M:M,Q:R").Select
Range("Q1").Activate
Range("D:F,H:H,I:I,J:J,K:K,L:L,M:M,Q:R,S:S,T:U").Select
Range("T1").Activate
Range
("D:F,H:H,I:I,J:J,K:K,L:L,M:M,Q:R,S:S,T:U,X:X").Select
Range("X1").Activate
Selection.Delete Shift:=xlToLeft
Columns("D").ColumnWidth = 11.43
Columns("E:E").ColumnWidth = 5
Columns("F:F").ColumnWidth = 20.14
Columns("G:G").ColumnWidth = 27.43
Columns("J:K").Select
Range("K1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="CANCELLED"
Rows("2:10000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2, Criteria1:="CANCELLED"
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=2
Columns("J:K").Select
Range("K1").Activate
Selection.AutoFilter
Columns("J:K").Select
Range("K1").Activate
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 1
Range("A2:H20000").Select
Selection.Sort Key1:=Range("B2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Columns("B:B").Select
Selection.NumberFormat = "m/d"
Next i
Unload progressDlg
End Sub
progress bar will update itself according to how far along
the macro is?
Sub Main()
Dim i As Long, tot As Long
tot = 100
progressDlg.Caption = "Prosessing data, please wait..."
For i = 1 To tot
If i Mod 5 = 0 Then ProgressBar i / tot
Application.ScreenUpdating = False
Sheets("RMA Log 2").Select
Application.Calculation = xlCalculationManual
Workbooks.Open Filename:="W:\BaanReports\Sales\rma
report.xls"
Cells.Select
Selection.Copy
Windows("RMA Reasons Report.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("rma report.xls").Activate
Application.CutCopyMode = False
ActiveWindow.Close
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Columns("D:F").Select
Range("D:F,H:H,I:I,J:J,K:K").Select
Range("K1").Activate
Range("D:F,H:H,I:I,J:J,K:K,L:L,M:M").Select
Range("M1").Activate
Range("D:F,H:H,I:I,J:J,K:K,L:L,M:M,Q:R").Select
Range("Q1").Activate
Range("D:F,H:H,I:I,J:J,K:K,L:L,M:M,Q:R,S:S,T:U").Select
Range("T1").Activate
Range
("D:F,H:H,I:I,J:J,K:K,L:L,M:M,Q:R,S:S,T:U,X:X").Select
Range("X1").Activate
Selection.Delete Shift:=xlToLeft
Columns("D").ColumnWidth = 11.43
Columns("E:E").ColumnWidth = 5
Columns("F:F").ColumnWidth = 20.14
Columns("G:G").ColumnWidth = 27.43
Columns("J:K").Select
Range("K1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="CANCELLED"
Rows("2:10000").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2, Criteria1:="CANCELLED"
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=2
Columns("J:K").Select
Range("K1").Activate
Selection.AutoFilter
Columns("J:K").Select
Range("K1").Activate
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 1
Range("A2:H20000").Select
Selection.Sort Key1:=Range("B2"),
Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Columns("B:B").Select
Selection.NumberFormat = "m/d"
Next i
Unload progressDlg
End Sub