Good afternoon,
Is there a way of having the formula that is being entered in to H9 be copied down the rows until the end of the data? The spreadsheet that is generated for me is not always going to have the same number of rows. I have looked at the post http://www.excelforum.com/showthread.php?t=624654 but am unsure how to incorporate it in to my code.......
Selection.AutoFill Destination:=Range("H9:H220"), Type:=xlFillDefault
Range("H9:H220").Select
Also, I am just wondering if there is a way to add the records under the script and target headings as 'next record' or something similar.
Range("K3").Select
ActiveCell.FormulaR1C1 = "Script"
Range("L3").Select
ActiveCell.FormulaR1C1 = "Target"
[B] Range("K4").Select
ActiveCell.FormulaR1C1 = "DL1 "
Range("L4").Select
ActiveCell.FormulaR1C1 = "0.32"
Range("K5").Select
ActiveCell.FormulaR1C1 = "DV9 "
Range("L5").Select
ActiveCell.FormulaR1C1 = "0.32"
This will mean that as new scripts get added or deleted, there are no blank spaces in this list and will make it easier to maintain on an ongoing basis.
Any help would be much appreciated.
Full copy of my macro is as follows:
Sub FormatLeaderBoard()
'
' FormatLeaderBoard Macro
' This macro will make the necessary changes to create the Leader Board
'
'
Cells.Select
Selection.RemoveSubtotal
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C:N,R:S").Select
Range("R1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("K3").Select
ActiveCell.FormulaR1C1 = "Script"
Range("L3").Select
ActiveCell.FormulaR1C1 = "Target"
Range("K4").Select
ActiveCell.FormulaR1C1 = "DL1 "
Range("L4").Select
ActiveCell.FormulaR1C1 = "0.32"
Range("K5").Select
ActiveCell.FormulaR1C1 = "DV9 "
Range("L5").Select
ActiveCell.FormulaR1C1 = "0.32"
Range("K6").Select
ActiveCell.FormulaR1C1 = "ED2 "
Range("L6").Select
ActiveCell.FormulaR1C1 = "0.77"
Range("H8").Select
ActiveCell.FormulaR1C1 = "% To Target"
Range("H8").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("H9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(R4C11:R23C11,RC[-6]),RC[-3]/VLOOKUP(RC[-6],R4C11:R23C12,2,0),"""")"
Range("H9").Select
Selection.AutoFill Destination:=Range("H9:H220"), Type:=xlFillDefault
Range("H9:H220").Select
Range("H8:H220").Select
Selection.NumberFormat = "0.00%"
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(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Bookmarks