Macro - very slow run in 2003

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
 
J

Jim Thomlinson

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...
 
M

murkaboris

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...
 
J

Jim Thomlinson

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Macro 7
Run-time error 1004 paste method of worksheet class failed 3
Excel Macro to use cells as a autofilter 0
Macro to copy/paste from one workbook to another 8
macro problem: range offset question 6
File Name changes with dates and Deleting Rows of Zeros 0
Hard reference in a macro 1
Macro to run on hidden sheet 4

Top