Help with Macro

J

Jeremy

How Do I change the Below to start Typing The first Name ALWAYS in M8, no
matter which cell is selected?




OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select
ActiveCell.FormulaR1C1 = "Alma"
Range("M10").Select
ActiveCell.FormulaR1C1 = "Anne"
Range("M11").Select
ActiveCell.FormulaR1C1 = "Billie"
Range("M12").Select
ActiveCell.FormulaR1C1 = "Georgeann"
Range("M13").Select
ActiveCell.FormulaR1C1 = "Jennifer"
Range("M14").Select
ActiveCell.FormulaR1C1 = "Jeremy"
Range("M15").Select
ActiveCell.FormulaR1C1 = "Joan"
Range("M16").Select
ActiveCell.FormulaR1C1 = "Karen"
Range("M17").Select
ActiveCell.FormulaR1C1 = "Lorena"
Range("M18").Select
ActiveCell.FormulaR1C1 = "Tom"
Range("Q7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.FormulaR1C1 = "Orders Older Than 24 Hours"
Range("R7").Select
ActiveCell.FormulaR1C1 = "Orders Less Than 24 Hours Old"
Columns("Q:R").Select
Columns("Q:R").EntireColumn.AutoFit
Range("Q8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders
Pending\Greater Than 24 Hour Formula.txt" _
, Destination:=Range("Q8"))
.Name = "Greater Than 24 Hour Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("Q8:Q18"), Type:=xlFillDefault
Range("Q8:Q18").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("R8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders Pending\Less
Than 24 HOur Formula.txt" _
, Destination:=Range("R8"))
.Name = "Less Than 24 HOur Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("R8:R18"), Type:=xlFillDefault
Range("R8:R18").Select
Columns("R:R").EntireColumn.AutoFit
Range("Q5:Q7").Select
Range("Q7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("Q:Q").ColumnWidth = 14.14
Range("R5:R7").Select
Range("R7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("R:R").ColumnWidth = 13.29
Columns("R:R").ColumnWidth = 14.57
Columns("Q:R").Select
Selection.ColumnWidth = 15
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Backlog"
With ActiveCell.Characters(Start:=1, Length:=13).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("Q20").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-2]C[1])"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal E"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Coordinator Backlog"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal ES Backlog"
Range("Q21").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-9]:R[379]C[-9],""=ZB"")"
Range("M22").Select
End Sub
 
J

Jim Thomlinson

In keeping with the rest of the code you could just change...

OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select

to

OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Range("M8").Select 'added this line
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select

--
HTH...

Jim Thomlinson


Jeremy said:
How Do I change the Below to start Typing The first Name ALWAYS in M8, no
matter which cell is selected?




OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select
ActiveCell.FormulaR1C1 = "Alma"
Range("M10").Select
ActiveCell.FormulaR1C1 = "Anne"
Range("M11").Select
ActiveCell.FormulaR1C1 = "Billie"
Range("M12").Select
ActiveCell.FormulaR1C1 = "Georgeann"
Range("M13").Select
ActiveCell.FormulaR1C1 = "Jennifer"
Range("M14").Select
ActiveCell.FormulaR1C1 = "Jeremy"
Range("M15").Select
ActiveCell.FormulaR1C1 = "Joan"
Range("M16").Select
ActiveCell.FormulaR1C1 = "Karen"
Range("M17").Select
ActiveCell.FormulaR1C1 = "Lorena"
Range("M18").Select
ActiveCell.FormulaR1C1 = "Tom"
Range("Q7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.FormulaR1C1 = "Orders Older Than 24 Hours"
Range("R7").Select
ActiveCell.FormulaR1C1 = "Orders Less Than 24 Hours Old"
Columns("Q:R").Select
Columns("Q:R").EntireColumn.AutoFit
Range("Q8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders
Pending\Greater Than 24 Hour Formula.txt" _
, Destination:=Range("Q8"))
.Name = "Greater Than 24 Hour Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("Q8:Q18"), Type:=xlFillDefault
Range("Q8:Q18").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("R8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders Pending\Less
Than 24 HOur Formula.txt" _
, Destination:=Range("R8"))
.Name = "Less Than 24 HOur Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("R8:R18"), Type:=xlFillDefault
Range("R8:R18").Select
Columns("R:R").EntireColumn.AutoFit
Range("Q5:Q7").Select
Range("Q7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("Q:Q").ColumnWidth = 14.14
Range("R5:R7").Select
Range("R7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("R:R").ColumnWidth = 13.29
Columns("R:R").ColumnWidth = 14.57
Columns("Q:R").Select
Selection.ColumnWidth = 15
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Backlog"
With ActiveCell.Characters(Start:=1, Length:=13).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("Q20").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-2]C[1])"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal E"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Coordinator Backlog"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal ES Backlog"
Range("Q21").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-9]:R[379]C[-9],""=ZB"")"
Range("M22").Select
End Sub
Click to expand...
 
J

Jeremy

I have a new faith in humanity after finding these discussion boards.

Thanks
jeremy

Jim Thomlinson said:
In keeping with the rest of the code you could just change...

OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select

to

OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Range("M8").Select 'added this line
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select

--
HTH...

Jim Thomlinson


Jeremy said:
How Do I change the Below to start Typing The first Name ALWAYS in M8, no
matter which cell is selected?




OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select
ActiveCell.FormulaR1C1 = "Alma"
Range("M10").Select
ActiveCell.FormulaR1C1 = "Anne"
Range("M11").Select
ActiveCell.FormulaR1C1 = "Billie"
Range("M12").Select
ActiveCell.FormulaR1C1 = "Georgeann"
Range("M13").Select
ActiveCell.FormulaR1C1 = "Jennifer"
Range("M14").Select
ActiveCell.FormulaR1C1 = "Jeremy"
Range("M15").Select
ActiveCell.FormulaR1C1 = "Joan"
Range("M16").Select
ActiveCell.FormulaR1C1 = "Karen"
Range("M17").Select
ActiveCell.FormulaR1C1 = "Lorena"
Range("M18").Select
ActiveCell.FormulaR1C1 = "Tom"
Range("Q7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.FormulaR1C1 = "Orders Older Than 24 Hours"
Range("R7").Select
ActiveCell.FormulaR1C1 = "Orders Less Than 24 Hours Old"
Columns("Q:R").Select
Columns("Q:R").EntireColumn.AutoFit
Range("Q8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders
Pending\Greater Than 24 Hour Formula.txt" _
, Destination:=Range("Q8"))
.Name = "Greater Than 24 Hour Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("Q8:Q18"), Type:=xlFillDefault
Range("Q8:Q18").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("R8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders Pending\Less
Than 24 HOur Formula.txt" _
, Destination:=Range("R8"))
.Name = "Less Than 24 HOur Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("R8:R18"), Type:=xlFillDefault
Range("R8:R18").Select
Columns("R:R").EntireColumn.AutoFit
Range("Q5:Q7").Select
Range("Q7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("Q:Q").ColumnWidth = 14.14
Range("R5:R7").Select
Range("R7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("R:R").ColumnWidth = 13.29
Columns("R:R").ColumnWidth = 14.57
Columns("Q:R").Select
Selection.ColumnWidth = 15
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Backlog"
With ActiveCell.Characters(Start:=1, Length:=13).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("Q20").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-2]C[1])"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal E"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Coordinator Backlog"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal ES Backlog"
Range("Q21").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-9]:R[379]C[-9],""=ZB"")"
Range("M22").Select
End Sub
Click to expand...
Click to expand...
 
R

Roger Govier

Hi Jeremy

Insert before the first line in your macro
ActiveSheet.Range("M8").Select
--
Regards
Roger Govier

Jeremy said:
How Do I change the Below to start Typing The first Name ALWAYS in M8, no
matter which cell is selected?




OOPR5 Macro
' Macro recorded 2/12/2008 by ko23693
'
' Keyboard Shortcut: Ctrl+Shift+O
'
ActiveCell.FormulaR1C1 = "Allie"
Range("M9").Select
ActiveCell.FormulaR1C1 = "Alma"
Range("M10").Select
ActiveCell.FormulaR1C1 = "Anne"
Range("M11").Select
ActiveCell.FormulaR1C1 = "Billie"
Range("M12").Select
ActiveCell.FormulaR1C1 = "Georgeann"
Range("M13").Select
ActiveCell.FormulaR1C1 = "Jennifer"
Range("M14").Select
ActiveCell.FormulaR1C1 = "Jeremy"
Range("M15").Select
ActiveCell.FormulaR1C1 = "Joan"
Range("M16").Select
ActiveCell.FormulaR1C1 = "Karen"
Range("M17").Select
ActiveCell.FormulaR1C1 = "Lorena"
Range("M18").Select
ActiveCell.FormulaR1C1 = "Tom"
Range("Q7").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.FormulaR1C1 = "Orders Older Than 24 Hours"
Range("R7").Select
ActiveCell.FormulaR1C1 = "Orders Less Than 24 Hours Old"
Columns("Q:R").Select
Columns("Q:R").EntireColumn.AutoFit
Range("Q8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders
Pending\Greater Than 24 Hour Formula.txt" _
, Destination:=Range("Q8"))
.Name = "Greater Than 24 Hour Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("Q8:Q18"), Type:=xlFillDefault
Range("Q8:Q18").Select
Columns("Q:Q").EntireColumn.AutoFit
Range("R8").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\ko23693\Desktop\Orders Pending\Less
Than 24 HOur Formula.txt" _
, Destination:=Range("R8"))
.Name = "Less Than 24 HOur Formula_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Selection.AutoFill Destination:=Range("R8:R18"), Type:=xlFillDefault
Range("R8:R18").Select
Columns("R:R").EntireColumn.AutoFit
Range("Q5:Q7").Select
Range("Q7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("Q:Q").ColumnWidth = 14.14
Range("R5:R7").Select
Range("R7").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Columns("R:R").ColumnWidth = 13.29
Columns("R:R").ColumnWidth = 14.57
Columns("Q:R").Select
Selection.ColumnWidth = 15
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Backlog"
With ActiveCell.Characters(Start:=1, Length:=13).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("Q20").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-12]C:R[-2]C[1])"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal E"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M20").Select
ActiveCell.FormulaR1C1 = "Total Coordinator Backlog"
Range("M21").Select
ActiveCell.FormulaR1C1 = "Toal ES Backlog"
Range("Q21").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-20]C[-9]:R[379]C[-9],""=ZB"")"
Range("M22").Select
End Sub
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

How to get a macro to run on all files in a folder and save each one? 0
Macro - very slow run in 2003 3
Modify Recorded Macro 1
toggeling Macro 3
Cannot select rows in VBA 1
Macro to Copy / paste from 1 workbook to another 6
Hard reference in a macro 1
Macro Running Slow 2

Top