I try and post the two macro I am working on. The first one format's the worksheet (TeamFormat) and the second one is the entry of formula to get data from an adjoining worksheet. I didn't use cut aand past because i am only getting data from every 5th line.
Sub TeamFormat()
' TeamFormat Macro
' Macro recorded 7/20/2004 by mike
Range("B1").Select
ActiveCell.FormulaR1C1 = "Team Name"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Event 1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Event 2"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Event 3"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Event 4"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Total"
Columns("B:B").Select
Selection.ColumnWidth = 20
Columns("C:G").Select
Selection.ColumnWidth = 8.5
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A3").Select
Selection.Copy
Range("A4:A102").Select
ActiveSheet.Paste
Range("A18").Select
ActiveWindow.LargeScroll Down:=1
Range("A51").Select
ActiveWindow.LargeScroll Down:=1
Range("A84").Select
ActiveWindow.LargeScroll Down:=1
Range("A117").Select
ActiveWindow.LargeScroll Down:=1
Range("A150").Select
ActiveWindow.LargeScroll Down:=1
Range("A183").Select
ActiveWindow.LargeScroll Down:=-1
Range("A150").Select
ActiveWindow.LargeScroll Down:=-1
Range("A106").Select
ActiveSheet.Paste
Range("A107").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("A107").Select
Selection.Copy
Range("A108:A204").Select
ActiveSheet.Paste
Range("B109").Select
ActiveWindow.SmallScroll Down:=-9
Range("A101:A102").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=87
Range("B105").Select
ActiveCell.FormulaR1C1 = "Team Name"
Range("C105").Select
ActiveCell.FormulaR1C1 = "Event 1"
Range("D105").Select
ActiveCell.FormulaR1C1 = "Event 2"
Range("E105").Select
ActiveCell.FormulaR1C1 = "Event 3"
Range("F105").Select
ActiveCell.FormulaR1C1 = "Event 4"
Range("G105").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B105:G105").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
Range("I108").Select
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
Range("I106").Select
End Sub
Sub TeamGetData()
' TeamGetData Macro
' Macro recorded 7/20/2004 by mike
Range("B106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Teamlist,2)"
Range("C106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,7)"
Range("C106").Select
Selection.Copy
Range("D106:G106").Select
ActiveSheet.Paste
Range("D106").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,8)"
Range("E106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,9)"
Range("F106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,10)"
Range("G106").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Teamscore,11)"
Range("B106:G106").Select
Selection.Copy
Range("B107:B204").Select
ActiveSheet.Paste
Range("B106:G204").Select
Range("G204").Activate
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("I12").Select
End Sub
These two macro were recorded 10min apart. I tested the TeamFormat a couple of times. then created the TeamGetData and some how between the two the cell reference style was changed.