ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro - very slow run in 2003 (https://www.excelbanter.com/excel-discussion-misc-queries/243939-macro-very-slow-run-2003-a.html)

murkaboris
Macro - very slow run in 2003
 
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

Jim Thomlinson
Macro - very slow run in 2003
 
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" wrote:

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

murkaboris
Macro - very slow run in 2003
 
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" wrote:

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" wrote:

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

Jim Thomlinson
Macro - very slow run in 2003
 
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" wrote:

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" wrote:

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" wrote:

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


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com