×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Procedure too large--help!! VB Novice

Procedure too large--help!! VB Novice

Procedure too large--help!! VB Novice

(OP)
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("D53:D57").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("P4:P8").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("P11:P15").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("P18:P22").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("P25:P29").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**

RE: Procedure too large--help!! VB Novice

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.

RE: Procedure too large--help!! VB Novice

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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources