What's wrong with this code?

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
Sub Print_Copies()

With ActiveSheet
If .Range("CellLink") = 1 Then
Call Print_1
ElseIf .Range("CellLink") = 2 Then
Call Print_2
ElseIf .Range("CellLink") = 3 Then
Call Print_3
ElseIf .Range("CellLink") = 4 Then
Call Print_4
ElseIf .Range("CellLink") = 5 Then
Call Print_5
If .Range("CellLink") = 6 Then
Call Print_6
ElseIf .Range("CellLink") = 7 Then
Call Print_7
If .Range("CellLink") = 8 Then
Call Print_8
ElseIf .Range("CellLink") = 9 Then
Call Print_9
If .Range("CellLink") = 10 Then
Call Print_10
ElseIf .Range("CellLink") = 11 Then
Call Print_11
If .Range("CellLink") = 12 Then
Call Print_12
ElseIf .Range("CellLink") = 13 Then
Call Print_13
If .Range("CellLink") = 14 Then
Call Print_14
ElseIf .Range("CellLink") = 15 Then
Call Print_15
If .Range("CellLink") = 16 Then
Call Print_16
ElseIf .Range("CellLink") = 17 Then
Call Print_17
If .Range("CellLink") = 18 Then
Call Print_18
ElseIf .Range("CellLink") = 19 Then
Call Print_19
ElseIf .Range("CellLink") = 20 Then
Call Print_20
End Sub


??? Any ideas??!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Jase

Care to share the solution?

By the way that code could be probably be condensed to this.
Code:
Sub Print_Copies()

With ActiveSheet
    Application.Run "Print_" & .Range("CellLink")
End With

End Sub
 

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
The solution was including the Else before every second If which I forgot to include when writing the first two lines. I then copied it. :oops:

What I am trying to achieve here is a drop down menu numbered 1 through 20 where the number relates to the copies the user wants to print.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So you have 20 different subs and the only difference is the number of copies the user wants to print?
 

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
That's correct, if there is a better way of doing this, I'm all eyes!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Well the code I posted should deal with calling the different subs.

But I'm not sure you actually need all the different subs, but that's hard to tell without seeing them and/or knowing exactly what they're meant to do.
 

Jase L

New Member
Joined
Sep 16, 2006
Messages
13
Pasted an example below - Print_1 is exactly the same as the 19 other, except the number of copies changes.





Sub Print_Copies()

With ActiveSheet
If .Range("CellLink") = 1 Then
Call Print_1
ElseIf .Range("CellLink") = 2 Then
Call Print_2
ElseIf .Range("CellLink") = 3 Then
Call Print_3
ElseIf .Range("CellLink") = 4 Then
Call Print_4
ElseIf .Range("CellLink") = 5 Then
Call Print_5
ElseIf .Range("CellLink") = 6 Then
Call Print_6
ElseIf .Range("CellLink") = 7 Then
Call Print_7
ElseIf .Range("CellLink") = 8 Then
Call Print_8
ElseIf .Range("CellLink") = 9 Then
Call Print_9
ElseIf .Range("CellLink") = 10 Then
Call Print_10
ElseIf .Range("CellLink") = 11 Then
Call Print_11
ElseIf .Range("CellLink") = 12 Then
Call Print_12
ElseIf .Range("CellLink") = 13 Then
Call Print_13
ElseIf .Range("CellLink") = 14 Then
Call Print_14
ElseIf .Range("CellLink") = 15 Then
Call Print_15
ElseIf .Range("CellLink") = 16 Then
Call Print_16
ElseIf .Range("CellLink") = 17 Then
Call Print_17
ElseIf .Range("CellLink") = 18 Then
Call Print_18
ElseIf .Range("CellLink") = 19 Then
Call Print_19
ElseIf .Range("CellLink") = 20 Then
Call Print_20
End If
End With

End Sub
Sub Print_1()
'
' Print_1 Macro
' Macro recorded 14/09/2006 by
'
Sheets("Quality Graph").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("TCHr Graph").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveChart.PlotArea.Select
Sheets("AHT Graph").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("PQ Graph").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,098
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top