Macro - very slow run in 2003

  • Thread starter Thread starter murkaboris
  • Start date Start date
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
 
When you add formuals it recalcs. When you filter it recalcs. that is
probably the worst offender for speed... Try this...

Sub Whatever()
With Application
..ScreenUpdating = False
..Calculation = xlCalculationManual
End With

'Your code

With Application
..ScreenUpdating = True
..Calculation = xlCalculationAutomatic
End With
end sub
--
HTH...

Jim Thomlinson


murkaboris said:
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
Click to expand...
 
Hello Jim:

thank you for your suggestion. I was hoping there was something "better"
than just switching to manual calc but I'll take that as well :).

Thanks again.
Monika

Jim Thomlinson said:
When you add formuals it recalcs. When you filter it recalcs. that is
probably the worst offender for speed... Try this...

Sub Whatever()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'Your code

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
end sub
--
HTH...

Jim Thomlinson


murkaboris said:
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
Click to expand...
Click to expand...
 
There are other things you can do such as modifying your existing code to
remove all of the selections and possibly others. Their effect will be only
marginal on the speed.
--
HTH...

Jim Thomlinson


murkaboris said:
Hello Jim:

thank you for your suggestion. I was hoping there was something "better"
than just switching to manual calc but I'll take that as well :).

Thanks again.
Monika

Jim Thomlinson said:
When you add formuals it recalcs. When you filter it recalcs. that is
probably the worst offender for speed... Try this...

Sub Whatever()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'Your code

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
end sub
--
HTH...

Jim Thomlinson


murkaboris said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top