Procedure too large

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
 
J

Joel

If I saw more of the code I could do better. Making Numberone to number
twnety an array will also help becuase you can use some loops.

num(1) = .Range("E500")
..
..
..
num(20) = .Range("D500")

.Cells(8, 3) = num(1)
for i = 32 to 20 step -1
.Cells(i, 7) = num(34 - i)
next i





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
With Sheets("jmf changes")
numone = .Range("E500")
numtwo = .Range("I500")
numthree = .Range("J500")
numfour = .Range("V500")
numfive = .Range("K500")
numsix = .Range("L500")
numseven = .Range("M500")
numeight = .Range("N500")
numnine = .Range("O500")
numten = .Range("P500")
numeleven = .Range("Q500")
numtwelve = .Range("R500")
numthirteen = .Range("S500")
numfourteen = .Range("T500")
numfifteen = .Range("X500")
numsixteen = .Range("Y500")
numseventeen = .Range("Z500")
numeighteen = .Range("AA500")
numnineteen = .Range("W500")
numtwenty = .Range("D500")
End With
With Sheets("Data worksheet")
.Cells(8, 3) = numone
.Cells(32, 7) = numtwo
.Cells(31, 7) = numthree
.Cells(30, 7) = numfour
.Cells(29, 7) = numfive
.Cells(28, 7) = numsix
.Cells(27, 7) = numseven
.Cells(26, 7) = numeight
.Cells(25, 7) = numnine
.Cells(24, 7) = numten
.Cells(23, 7) = numeleven
.Cells(22, 7) = numtwelve
.Cells(21, 7) = numthirteen
.Cells(20, 7) = numfourteen
.Cells(99, 42) = numfifteen
.Cells(99, 39) = numsixteen
.Cells(99, 40) = numseventeen
.Cells(99, 41) = numeighteen
.Cells(99, 43) = numnineteen
.Cells(65, 3) = numtwenty
End With
'***********Populate Graph*****************

With Sheets("jmf changes")

.Range("M11:M500").Copy
Sheets("9_5mm").Range("C16") _
.Selection.PasteSpecial Paste:=xlValues

.Range("V11:V500").Copy
Sheets("25mm chart").Range("C16") _
.Selection.PasteSpecial Paste:=xlValues

.Range("K11:K500").Copy
Sheets("3_4 in Chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("L11:L500").Copy
Sheets("1_2 in chart").Range ("C16")
.PasteSpecial Paste:=xlValues

.Range("T11:T500").Copy
Sheets("#200_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("O11:O500").Copy
Sheets("#8_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("N11:N500").Copy
Sheets("#4_chart (2)").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("AD11:AD500").Copy
Sheets("dust_ac").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("H11:H500").Copy
Sheets("vfa_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("G11:G500").Copy
Sheets("vma_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("F11:F500").Copy
Sheets("vtm_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("E11:E500").Copy
Sheets("ac_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("U11:U500").Copy
Sheets("gmm_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("W11:W500").Select
Sheets("gse_chart").Range("C16") _
.PasteSpecial Paste:=xlValues

.Range("X11:X500").Copy
Sheets("rice_chart").Range("C16") _
.PasteSpecial Paste:=xlValues
End With
End Select
End Sub
 
R

RB Smissaert

And all this could be replaced by an array:

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

Do instead:

Dim arrDoubles(1 to 20) as Double


RBS
 
R

Rick Rothstein \(MVP - VB\)

Something for you to study...

Assuming I made no mistakes in transcribing your code, the following
(heavily commented code) can replace all the code you posted in your
message...

Sub Limits()
Dim X As Long
Dim Mix_Size As Long
Dim RowNums(1 To 20) As Long
Dim ColNums(1 To 20) As Long
Dim StartSheetNum As String
Dim EndSheetNum As String
Dim GraphCell As String
Dim GraphSheets() As String
Dim ColLetters() As String

Sheets("Data worksheet").Unprotect "1dickson"

' The graph sheets are assumed to be the same for all cases, so
' we define them outside of the loop for reference inside the loop
GraphSheets = Split("9_5mm,25mm chart,3_4 in chart,1_2 in chart," & _
"#200_chart,#8_chart,#4_chart,dust_ac," & _
"vfa_chart,vma_chart,vtm_chart,ac_chart," & _
"gmm_chart,gse_chart,rice_chart", ",")

Mix_Size = Range("Mix_Size").Value
Select Case Mix_Size
Case 1 '4052
With Sheets("JMF Changes")
' The row and column numbers are "coordinated", that is,
' the first row and column numbers are used together in
' the "Data worksheet" Cells command; same for the second
' row and column numbers, and so on through the 20th of each
RowNums = Array(8, 32, 31, 30, 29, 28, 27, 26, 25, 24, _
23, 22, 21, 20, 99, 99, 99, 99, 99, 65)
ColNums = Array(3, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, _
7, 7, 7, 42, 39, 40, 41, 43, 3)
' The column numbers used in the Range command below (from
' the "JMF Changes" sheet) are also coordinated, from 1 to
' 20, to match up with their row and column numbers for the
' above Cell command call
ColLetters = Split("E I J V K L M N O P Q R S T X Y Z AA W D")
' The sheet number for the Range command seems to always
' be 500 for this Case block
EndSheetNum = "500"
' Assign the 20 values from the "JMF Changes" Ranges
' to the "Data worksheet" Cells
For X = 0 To 19
Sheets("Data worksheet").Cells(RowNums(X), ColNums(X)) = _
.Range(ColLetter(X) & EndSheetNum)
Next
' The "JMF Changes" Ranges for the graphs (sheet names
' assigned prior to the start of the For-Next loop) seem
' to always start at 11 and end at 500 for this Case block
' (Note: The 500 was assigned earlier in this Case block)
StartSheetNum = "11"
GraphCell = "C16"
' The Column letters for the Graphs are coordinated with
' the graph sheet names; that is, Column M goes with graph
' sheet "9_5mm", Column V goes with graph sheet "25mm chart"
' and so on. (Note: We are reusing the ColLetters array)
ColLetters = Split("M V K L T O N A H G F E U W X")
'***********Populate Graph*****************
For X = 0 To 15
Sheets(GraphSheets(X)).Range(GraphCell).Value = _
.Range(ColLetters(X) & StartSheetNum & ":" & _
ColLetters(X) & EndSheetNum).Value
Next
End With

' The rest of your case should be able to be set up similarly
Case 2
'......
'......
End Select

End Sub


Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Case Select 4
Run macro from active workbook whose name keeps changing 20
Copy Paste Macro not Pasting Values when Blank 3
Copy paste - Values - formula - Optimise 2
Error using offset 1
sort macro not working 1

Top