Macro code is too long and will not run

nachousa

New Member
Joined
Jun 11, 2010
Messages
47
I have a code that is way too long so I keep getting the error: "Procedure too large". Is there a way to fix it? I dont know how to write the code another way.

This is my code. This is just for 1. There are about 37. Maybe there's another way to do it.
the code is just if "K7" = 1 then do ALL that. (goes up to if "K7" = 37 do something, and maybe more)
The code works fine if I only do around 10 but anymore more than that is too much.

Any Ideas?

Code:
Sheets("Sheet3").Select
If Range("K7").Value = 1 Then
Sheets("sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW01 AWP Baltimore"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G6").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
Sheets("sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW02 Norfolk"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G7").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
 
Sheets("sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW03 AWP Charleston"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G8").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW04 AWP HOU Gulfstream"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G9").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW05 New Orleans Rail"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
         .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G10").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW06 AWP Mobile"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G11").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW07 AWP San Francisco"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G12").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
Sheets("Sheet1").Select
Rows("1:1").Select
ActiveSheet.AutoFilterMode = False
With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=1, Criteria1:="AW08 AWP Portland"
        ActiveSheet.Range("$A$1:$AL$65000").AutoFilter field:=31, Criteria1:=Array("Consig. Transit", "Damage", "Good", "QI", "Sales"), Operator:=xlFilterValues
        .AutoFilter field:=5, Criteria1:="01/2009"
End With
With Range("AM1")
   .Formula = "=subtotal(9,h:h)"
   .Value = .Value
End With
Selection.AutoFilter
Sheets("INV").Range("G13").Value = Sheets("Sheet1").Range("AM1").Value
Sheets("INV").Select
 
Last edited:

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

nachousa

New Member
Joined
Jun 11, 2010
Messages
47
you can ignore the code. I just want to know if there's a way to run the code when is this long.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,135
Messages
5,509,373
Members
408,730
Latest member
Kayslover

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top