Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Help with Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 85
Default Help with Macro
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,939
Default Help with Macro
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" wrote:

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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 85
Default Help with Macro
I have a new faith in humanity after finding these discussion boards.

Thanks
jeremy

"Jim Thomlinson" wrote:

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

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,480
Default Help with Macro
Hi Jeremy

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

"Jeremy" <jeremiah.a.reynolds @ gmail.com wrote in message
...
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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
need help to update macro to office 2007 macro enabled workbook jatman Excel Discussion (Misc queries) 1 December 14th 07 01:57 PM
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 08:22 PM
using a cell value to control a counter inside a macro and displaying macro value ocset Excel Worksheet Functions 1 September 10th 06 05:32 AM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 1 June 11th 05 12:44 AM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement