M
murkaboris
Hello:
I've been using a macro for a while that was recorded in Excel 2003. Used to
run like a charm. I had to re-record it due to the slightly different
requirement by adding couple of columns - 4 of them are using vlookup and one
if functions.
They run extremely slow. But even if I would be ok with that once its done
anything else you try to do is taking forever. Simple fitlering takes 5 min
per filter....
Any advise? Thank you in advance.
Here is the macro for those added columns:
Range("U3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-18],'[Revenue Call Detail.xls]BL
Upside'!R2C4:R32C47,2,FALSE)"
Selection.Copy
Range("U4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.AutoFilter Field:=21, Criteria1:="#N/A"
Range("U3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=21
Range("V3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-19],'[Revenue Call Detail.xls]BL
Risk'!R2C4:R11C47,2,FALSE)"
Selection.Copy
Range("V4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("V2").Select
Selection.AutoFilter Field:=22, Criteria1:="#N/A"
Range("V3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=22
Range("W3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-20],'[Revenue Call Detail.xls]BL Called
Out'!R2C4:R37C47,2,FALSE)"
Selection.Copy
Range("W4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.AutoFilter Field:=23, Criteria1:="#N/A"
Range("W3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=23
Range("X3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-21],'HFS Deals'!RC[-22]:R[34]C[-5],17,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-21],'HFS
Deals'!R3C3:R36C25,22,FALSE)"
Range("X4").Select
Selection.Copy
Range("X4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("X3").Select
Selection.AutoFilter Field:=24, Criteria1:="#N/A"
Range("X3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=24
Range("S3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("S3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("Q:Q").EntireColumn.AutoFit
Columns("Q:Q").ColumnWidth = 7.57
Columns("Q:Q").ColumnWidth = 7.14
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Key2:=Range("S3") _
, Order2:=xlAscending, Key3:=Range("AF3"), Order3:=xlAscending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("L3").Select
ActiveWindow.FreezePanes = True
Range("B1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.15)
.FooterMargin = Application.InchesToPoints(0.15)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Columns("AA:AA").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("2:2").Select
Range("B2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("B2").Select
ActiveWorkbook.Save
End Sub
Thanks
Monika
I've been using a macro for a while that was recorded in Excel 2003. Used to
run like a charm. I had to re-record it due to the slightly different
requirement by adding couple of columns - 4 of them are using vlookup and one
if functions.
They run extremely slow. But even if I would be ok with that once its done
anything else you try to do is taking forever. Simple fitlering takes 5 min
per filter....
Any advise? Thank you in advance.
Here is the macro for those added columns:
Range("U3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-18],'[Revenue Call Detail.xls]BL
Upside'!R2C4:R32C47,2,FALSE)"
Selection.Copy
Range("U4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.AutoFilter Field:=21, Criteria1:="#N/A"
Range("U3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=21
Range("V3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-19],'[Revenue Call Detail.xls]BL
Risk'!R2C4:R11C47,2,FALSE)"
Selection.Copy
Range("V4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("V2").Select
Selection.AutoFilter Field:=22, Criteria1:="#N/A"
Range("V3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=22
Range("W3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-20],'[Revenue Call Detail.xls]BL Called
Out'!R2C4:R37C47,2,FALSE)"
Selection.Copy
Range("W4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.AutoFilter Field:=23, Criteria1:="#N/A"
Range("W3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=23
Range("X3").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-21],'HFS Deals'!RC[-22]:R[34]C[-5],17,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-21],'HFS
Deals'!R3C3:R36C25,22,FALSE)"
Range("X4").Select
Selection.Copy
Range("X4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("X3").Select
Selection.AutoFilter Field:=24, Criteria1:="#N/A"
Range("X3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.AutoFilter Field:=24
Range("S3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("S3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("Q:Q").EntireColumn.AutoFit
Columns("Q:Q").ColumnWidth = 7.57
Columns("Q:Q").ColumnWidth = 7.14
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Key2:=Range("S3") _
, Order2:=xlAscending, Key3:=Range("AF3"), Order3:=xlAscending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("L3").Select
ActiveWindow.FreezePanes = True
Range("B1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.15)
.FooterMargin = Application.InchesToPoints(0.15)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Columns("AA:AA").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("2:2").Select
Range("B2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("B2").Select
ActiveWorkbook.Save
End Sub
Thanks
Monika