E
Eric
I am running a select case statement but have 28 different cases. Each case
has 50 copy pastes. When I run this macro I get an error "Procedure too
Large" , how can I stop this?
Here is a copy of my first case (there needs to be 27 more after this one)
sub limits()
Dim Mix_Size As Integer
Mix_Size = Range("Mix_Size").Value
Sheets("Data worksheet").Select
Sheets("data worksheet").unprotect "1dickson"
Dim numone As Double
Dim numtwo As Double
Dim numthree As Double
Dim numfour As Double
Dim numfive As Double
Dim numsix As Double
Dim numseven As Double
Dim numeight As Double
Dim numnine As Double
Dim numten As Double
Dim numeleven As Double
Dim numtwelve As Double
Dim numthirteen As Double
Dim numfourteen As Double
Dim numfifteen As Double
Dim numsixteen As Double
Dim numseventeen As Double
Dim numeighteen As Double
Dim numninteen As Double
Dim numtwenty As Double
Select Case Mix_Size
Case 1 '4052
numone = Sheets("jmf changes").Range("E500")
numtwo = Sheets("jmf changes").Range("I500")
numthree = Sheets("jmf changes").Range("J500")
numfour = Sheets("jmf changes").Range("V500")
numfive = Sheets("jmf changes").Range("K500")
numsix = Sheets("jmf changes").Range("L500")
numseven = Sheets("jmf changes").Range("M500")
numeight = Sheets("jmf changes").Range("N500")
numnine = Sheets("jmf changes").Range("O500")
numten = Sheets("jmf changes").Range("P500")
numeleven = Sheets("jmf changes").Range("Q500")
numtwelve = Sheets("jmf changes").Range("R500")
numthirteen = Sheets("jmf changes").Range("S500")
numfourteen = Sheets("jmf changes").Range("T500")
numfifteen = Sheets("jmf changes").Range("X500")
numsixteen = Sheets("jmf changes").Range("Y500")
numseventeen = Sheets("jmf changes").Range("Z500")
numeighteen = Sheets("jmf changes").Range("AA500")
numnineteen = Sheets("jmf changes").Range("W500")
numtwenty = Sheets("jmf changes").Range("D500")
Sheets("Data worksheet").Cells(8, 3) = numone
Sheets("data worksheet").Cells(32, 7) = numtwo
Sheets("Data Worksheet").Cells(31, 7) = numthree
Sheets("Data Worksheet").Cells(30, 7) = numfour
Sheets("Data Worksheet").Cells(29, 7) = numfive
Sheets("Data Worksheet").Cells(28, 7) = numsix
Sheets("Data Worksheet").Cells(27, 7) = numseven
Sheets("Data Worksheet").Cells(26, 7) = numeight
Sheets("Data Worksheet").Cells(25, 7) = numnine
Sheets("Data Worksheet").Cells(24, 7) = numten
Sheets("Data Worksheet").Cells(23, 7) = numeleven
Sheets("Data Worksheet").Cells(22, 7) = numtwelve
Sheets("Data Worksheet").Cells(21, 7) = numthirteen
Sheets("Data Worksheet").Cells(20, 7) = numfourteen
Sheets("data worksheet").Cells(99, 42) = numfifteen
Sheets("Data Worksheet").Cells(99, 39) = numsixteen
Sheets("Data Worksheet").Cells(99, 40) = numseventeen
Sheets("Data Worksheet").Cells(99, 41) = numeighteen
Sheets("Data Worksheet").Cells(99, 43) = numnineteen
Sheets("Data Worksheet").Cells(65, 3) = numtwenty
'***********Populate Graph*****************
Sheets("JMF Changes").Select
Range("M11:M500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("9_5mm").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("V11:V500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("25mm chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("K11:K500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3_4 in Chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("L11:L500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("1_2 in chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("T11:T500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#200_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("O11:O500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#8_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("N11:N500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#4_chart (2)").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("AD11:AD500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("dust_ac").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("H11:H500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("vfa_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("G11:G500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("vma_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("F11:F500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("vtm_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("E11:E500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ac_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("U11:U500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("gmm_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("W11:W500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("gse_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("X11:X500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("rice_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Any help would be greatly appreciated. Thank you in advance.... Eric
has 50 copy pastes. When I run this macro I get an error "Procedure too
Large" , how can I stop this?
Here is a copy of my first case (there needs to be 27 more after this one)
sub limits()
Dim Mix_Size As Integer
Mix_Size = Range("Mix_Size").Value
Sheets("Data worksheet").Select
Sheets("data worksheet").unprotect "1dickson"
Dim numone As Double
Dim numtwo As Double
Dim numthree As Double
Dim numfour As Double
Dim numfive As Double
Dim numsix As Double
Dim numseven As Double
Dim numeight As Double
Dim numnine As Double
Dim numten As Double
Dim numeleven As Double
Dim numtwelve As Double
Dim numthirteen As Double
Dim numfourteen As Double
Dim numfifteen As Double
Dim numsixteen As Double
Dim numseventeen As Double
Dim numeighteen As Double
Dim numninteen As Double
Dim numtwenty As Double
Select Case Mix_Size
Case 1 '4052
numone = Sheets("jmf changes").Range("E500")
numtwo = Sheets("jmf changes").Range("I500")
numthree = Sheets("jmf changes").Range("J500")
numfour = Sheets("jmf changes").Range("V500")
numfive = Sheets("jmf changes").Range("K500")
numsix = Sheets("jmf changes").Range("L500")
numseven = Sheets("jmf changes").Range("M500")
numeight = Sheets("jmf changes").Range("N500")
numnine = Sheets("jmf changes").Range("O500")
numten = Sheets("jmf changes").Range("P500")
numeleven = Sheets("jmf changes").Range("Q500")
numtwelve = Sheets("jmf changes").Range("R500")
numthirteen = Sheets("jmf changes").Range("S500")
numfourteen = Sheets("jmf changes").Range("T500")
numfifteen = Sheets("jmf changes").Range("X500")
numsixteen = Sheets("jmf changes").Range("Y500")
numseventeen = Sheets("jmf changes").Range("Z500")
numeighteen = Sheets("jmf changes").Range("AA500")
numnineteen = Sheets("jmf changes").Range("W500")
numtwenty = Sheets("jmf changes").Range("D500")
Sheets("Data worksheet").Cells(8, 3) = numone
Sheets("data worksheet").Cells(32, 7) = numtwo
Sheets("Data Worksheet").Cells(31, 7) = numthree
Sheets("Data Worksheet").Cells(30, 7) = numfour
Sheets("Data Worksheet").Cells(29, 7) = numfive
Sheets("Data Worksheet").Cells(28, 7) = numsix
Sheets("Data Worksheet").Cells(27, 7) = numseven
Sheets("Data Worksheet").Cells(26, 7) = numeight
Sheets("Data Worksheet").Cells(25, 7) = numnine
Sheets("Data Worksheet").Cells(24, 7) = numten
Sheets("Data Worksheet").Cells(23, 7) = numeleven
Sheets("Data Worksheet").Cells(22, 7) = numtwelve
Sheets("Data Worksheet").Cells(21, 7) = numthirteen
Sheets("Data Worksheet").Cells(20, 7) = numfourteen
Sheets("data worksheet").Cells(99, 42) = numfifteen
Sheets("Data Worksheet").Cells(99, 39) = numsixteen
Sheets("Data Worksheet").Cells(99, 40) = numseventeen
Sheets("Data Worksheet").Cells(99, 41) = numeighteen
Sheets("Data Worksheet").Cells(99, 43) = numnineteen
Sheets("Data Worksheet").Cells(65, 3) = numtwenty
'***********Populate Graph*****************
Sheets("JMF Changes").Select
Range("M11:M500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("9_5mm").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("V11:V500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("25mm chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("K11:K500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("3_4 in Chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("L11:L500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("1_2 in chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("T11:T500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#200_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("O11:O500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#8_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("N11:N500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("#4_chart (2)").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("AD11:AD500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("dust_ac").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("H11:H500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("vfa_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("G11:G500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("vma_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("F11:F500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("vtm_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("E11:E500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ac_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("U11:U500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("gmm_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("W11:W500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("gse_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("JMF Changes").Select
Range("X11:X500").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("rice_chart").Select
Range("C16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Any help would be greatly appreciated. Thank you in advance.... Eric