Procedure too large--help!! VB Novice

W

washingtons

Registered User.
Local time
Today, 12:28
Joined
Jun 5, 2007
Messages
13
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**
 
You need to learn VB. You'll make your life a lot easier.
 
How do..

How do I do that??
 
Try going to Google and typing in VBA Tutorial, or Excel VBA Tutorial. There's lots of info out there.
 
I've written some pretty complicated stuff, and never hit a size limit. 2 general thoughts to keep in mind as you follow Bob's advice to find tutorials. It looks like there may be some repetitive code in there. If you can break tasks out to a separate function and call it, you'd save a bunch of code. In other words a new function may accept a sheet name as a parameter, then with that sheet name do various things to that sheet. That way you only have that code once, instead of once for every sheet.

Failing that, I suppose you could string multiple functions together. The code would start in function 1, which at its end would call function 2, etc.
 
vba

Just make another routine and call it from the first

call NextRoutine
 
You must log in or register to reply here.

Similar threads

G
Extract correct leave time
Replies
2
Views
724
goncalo
G
C
Divide code to avoid "Procedure too large error"
Replies
18
Views
2,558
The_Doc_Man
The_Doc_Man
J
Solved Convert An Excel Macro To Run In Access VBA
Replies
12
Views
1,850
jo15765
J
A
Is there a faster alternative to a for-if loop in VBA?
Replies
3
Views
1,521
AlexO_Leary
A
N
Column names and multi-cells to single cell
Replies
3
Views
1,045
Peter Reid
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom