Procedure too large--help!! VB Novice 1

[Washingtons]

I'm extremely new to Visual Basics and instead of learning how to use it, am trying to record an extremely long macro. It told me my procedure is too large and I'm only 1/4 of the way done with my macro. I have no idea how to begin using visual basic and need any help I can get to either shorten the code or break it up. I'm trying to move data from one sheet to another, calculate a function based on the data, and color the box of information using conditional formatting. I'll paste a bit of the code below:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/5/2007 by xqfr67
'

'
Selection.Font.ColorIndex = 3
ActiveCell.FormulaR1C1 = "sadasd"
Range("L100").Select
Selection.ClearContents
Selection.Font.ColorIndex = 3
Selection.Interior.ColorIndex = xlNone
Range("M106").Select
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Selection.AutoFilter Field:=6, Criteria1:="0"
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("C51").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)/R[-6]C"
Range("C52").Select
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("F61:F62").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("C61:C94").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("B53:B57").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=6
Range("C54").Select
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("G61:G94").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("C53:C57").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("S61:S94").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("D5357").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C58").Select
Selection.FormulaR1C1 = ""
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
Range("C58").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)/R[-6]C"
Range("C59").Select
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
ActiveWindow.SmallScroll Down:=-15
Selection.AutoFilter Field:=5
ActiveWindow.SmallScroll Down:=-18
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
ActiveWindow.SmallScroll Down:=-27
Selection.AutoFilter Field:=5, Criteria1:="1Q07"
Selection.AutoFilter Field:=6, Criteria1:="199"
Range("A1:U92").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
ActiveWindow.SmallScroll Down:=-12
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Rows("1:1").Select
ActiveSheet.ShowAllData
Range("F10").Select
Selection.AutoFilter Field:=5, Criteria1:="1Q08"
Range("A1:U92").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter Field:=6, Criteria1:="249"
Range("C83:C96").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
ActiveWindow.SmallScroll Down:=-21
Range("N4:N8").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("G83:G96").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("O4:O8").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("S83:S96").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("P48").Select
ActiveSheet.Paste
Range("O9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)/R[-6]C"
Range("O10").Select
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Selection.AutoFilter Field:=6, Criteria1:="199"
Range("C75:C86").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("N11:N15").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("G75:G86").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("O11:O15").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("S75:S86").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("P1115").Select
ActiveSheet.Paste
Range("O16").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)/R[-6]C"
Range("O17").Select
ActiveWorkbook.Save
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Selection.AutoFilter Field:=6, Criteria1:="149"
Range("C67:C72").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("N18:N22").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("G67:G72").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("O18:O22").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("S67:S72").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("P1822").Select
ActiveSheet.Paste
Range("O23").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)/R[-6]C"
Range("O24").Select
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Selection.AutoFilter Field:=6, Criteria1:="99"
Range("C71:C95").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("N25:N29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("G71:G95").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("O25:O29").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("S71:S95").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("P2529").Select
ActiveSheet.Paste
Range("O30").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)/R[-6]C"
Range("O31").Select
ActiveWorkbook.Save
Windows("2007 Intern Goals.xls").Activate
Application.Run "Toolbar_ReWrite"
Application.WindowState = xlMinimized
ActiveWindow.Close
Application.Run "Toolbar_ReWrite"
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
Range("I117").Select
ActiveSheet.ShowAllData
Range("F24").Select
ActiveWindow.SmallScroll Down:=-12
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
ActiveWindow.SmallScroll Down:=-12
Range("K12").Select
ActiveWindow.SmallScroll Down:=0
Cells.Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlDescending, Key3:=Range("G2"), Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("J22").Select
ActiveWindow.SmallScroll Down:=42
Range("J57").Select
ActiveWindow.SmallScroll Down:=-42
Range("K14:L14").Select
Range("L14").Activate
ActiveWindow.SmallScroll Down:=-15
Range("K13").Select
ActiveWindow.SmallScroll Down:=36
Sheets("Sheet3").Select
Application.Run "Toolbar_ReWrite"
Sheets("Sheet2").Select
Application.Run "Toolbar_ReWrite"
Range("A1:M58").Select
Selection.Copy
Sheets("Sheet3").Select
Application.Run "Toolbar_ReWrite"
Cells.Select
ActiveSheet.Paste
Range("N10").Select
Columns("A:A").EntireColumn.AutoFit
Range("M14").Select
ActiveWindow.SmallScroll Down:=-9
Sheets("Sheet1").Select
Application.Run "Toolbar_ReWrite"
ActiveWindow.SmallScroll Down:=0
Range("E46").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2Q07"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("E46").Select
Selection.AutoFill Destination:=Range("E46:E51"), Type:=xlFillDefault
Range("E46:E51").Select
Range("E46").Select
**This is far from the end of it**

 

[handleman]

Just looking at the first 20 lines or so of this code, it appears that you are being extrememly inefficient in your actions while recording. Remember, the macro recorder records exactly everything you do, not the results you eventually get. That means that every time you switch sheets, every time you scroll, every time you change selections it records that action. Just about the only action it doesn't record is your mouse movements or actions performed in other applications. You need to carefully plan ahead and only perform those actions that you want to be repeated exactly in the macro.

Or you could learn VBA.

 

[cowski]

Also you can record multiple macros and combine them when you are done. Break up the end goal into sensible chunks such as data entry, formula entry, formatting, etc etc. Now record a macro for each chunk. With very little VBA knowledge you can now combine these macros to do the entire task. In the code editor make a subroutine (name doesn't matter) such as:

sub DoItAll()
call Macro1
call Macro2
call Macro3
.
.
.
end sub

Now to replay the entire set just call the macro "DoItAll" or whatever name you gave it.