macro performance not consistent

[ap1]

I am running the macro for the same data over and over and it seems that some times it takes 10 secs to execute and some times more than a minute. do u know why?

[Mudface]

No. Because you haven't said what your macro is, what data it's running on, what other files you may or may not have open at the time, what your system resources are when running the macro and whether or not it's a full moon.

[Jack in the UK]

HI

To add to Mudface

What Windows aew you running?
Memeory?

But also wahts the script REALLY doing, ie :
Saving docs
Converting dosc
Paste to Word
Converting data
Copy and Paste

List A to Z of what the script is doing, like above and we can *****.

Also why so many runs, poss above will cover this.

[ap1]

I am running a daf query which creates a TXT file. Then I am opening that txt file using an excel template.
My macro is doing following things:
Within the template macros is written to open the txt file then format it.

[ap1]

Sub Auto_Open()

'
' Macro3 Macro
' Macro recorded 03/13/2002 by Aetna Inc.
'

'
Application.ScreenUpdating = False
Application.StatusBar = "Macro Running in Background Please Wait....."
Workbooks.OpenText Filename:="C:ERA_EFTEPDB_BILLING.TXT", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1))
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "EPDB PIN"
Range("B1").Select
ActiveCell.FormulaR1C1 = ""
Range("A1").Select
Selection.ClearContents
Range("D1").Select
ActiveCell.FormulaR1C1 = "EPDB PIN"
Range("E1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Unit #"
Range("F1").Select
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("F1").Select
ActiveCell.FormulaR1C1 = "EPDB Provider Name"
Range("F2").Select
Columns("F:F").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
Range("G2").Select
Columns("G:G").EntireColumn.AutoFit
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("H1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Bldg NM"
Range("H2").Select
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Range("I1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 1"
Range("J1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 2"
Range("J2").Select
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Range("K1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr City"
Range("L1").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Addr State"
Range("M1").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
ActiveCell.FormulaR1C1 = "EPDB Billing Addr Zip"
Range("N1").Select
Columns("M:M").EntireColumn.AutoFit
Columns("M:M").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=-3
ActiveWindow.LargeScroll ToRight:=-1
Range("D1").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.LargeScroll ToRight:=2
Range("D1:M1").Select
Selection.Font.Bold = True
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=8
ActiveWindow.ScrollColumn = 1
Cells.Select
ActiveWindow.SmallScroll ToRight:=6
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.ScrollColumn = 8
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Key2:=Range("J2") _
, Order2:=xlAscending, Key3:=Range("I2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Range("D3").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
Range("D3:M3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
Columns("A:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = True
Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.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 = 160
End With
ActiveWindow.LargeScroll Down:=0
Columns("F:F").ColumnWidth = 75.71
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("F:F").ColumnWidth = 38.57
ActiveWindow.LargeScroll Down:=23
Range("I791").Select
ActiveWindow.LargeScroll Down:=-23
ActiveWindow.SmallScroll ToRight:=6
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.LargeScroll ToRight:=-3
Columns("E:E").EntireColumn.AutoFit
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 160
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
Columns("H:H").ColumnWidth = 28.29
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").ColumnWidth = 27.71
ActiveWindow.SmallScroll ToRight:=1
Columns("J:J").ColumnWidth = 24.86
ActiveWindow.SmallScroll ToRight:=2
ActiveWindow.LargeScroll ToRight:=-2
Range("D5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("D1").Select
ActiveWindow.LargeScroll ToRight:=1
Range("D1:M4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D6").Select
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
ActiveWindow.SmallScroll ToRight:=1
Range("E5").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = "&D Report Ran"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 180
End With
Columns("G:G").ColumnWidth = 17.57
ActiveWindow.SmallScroll ToRight:=-2
Range("G4").Select
Selection.EntireRow.Insert
Range("G5").Select
ActiveCell.FormulaR1C1 = "EPDB Billing Address"
Range("G6").Select
ActiveCell.FormulaR1C1 = "Number"
Range("G5:M5").Select
ActiveWindow.LargeScroll ToRight:=-2
Range("G5").Select
Selection.ClearContents
Rows("5:5").Select
Range("D5").Activate
Selection.Delete Shift:=xlUp
Range("G5").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
Range("H9").Select
Columns("G:G").ColumnWidth = 18.29
Columns("G:G").ColumnWidth = 18
Range("D3:M3").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE("" Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
Range("D4").Select
Cells.Select
Range("D1").Activate
ActiveSheet.PageSetup.PrintArea = Selection.Address
Application.StatusBar = False
Range("D4").Select
End Sub