How to assign a value as a Named Area

[et]

I would like to write a VBA to make some copies automatically. The range is
to be selected based on a cell's value.

For example :
if A1 = "General Admin" Then print area named "GA"
if A1 = "Sales Admin" Then print area named "SA"

My VBA is :
Dim x As String
If ActiveCell.Value = "General Management" Then
x = "GA"
ElseIf ActiveCell.Value = "Sales Admin." Then
x = "SA"
ElseIf ActiveCell.Value = "Beijing" Then
x = "BJ"
ElseIf ActiveCell.Value = "Shanghai" Then
x = "SH"
End If

Range(x).Select

I guess I chose the wrong data type but not sure which one is correct. Could
anyone help, thanks.

et

 

[Dave Peterson]

Sometimes when you get a long list of things to check, it's easier to use the
"select case/end select" structure. I think it makes it a little easier to
read.

Option Explicit
Sub testme()

Dim x As String

With ActiveSheet
Select Case LCase(.Range("a1").Value)
Case Is = LCase("general management"): x = "ga"
Case Is = LCase("Sales Admin"): x = "sa"
Case Is = lCase("Beijing"): x = "bj"
Case Is = LCase("Shanghai"): x = "sh"
Case Else
x = ""
End Select
If x = "" Then
'do nothing
Else
.Range(x).PrintOut preview:=True
End If
End With

End Sub

 

[somethinglikeant]

Sub PrintWhichArea()
Select Case [A1] 'chooses different print areas dependant on value of
A1
Case "General Management"
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("GA")
Case "Sales Admin."
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("SA")
Case "Beijing"
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("BJ")
Case "Shanghai"
ActiveWorkbook.Names.Add Name:="Print_Area",
RefersToR1C1:=Range("SH")
End Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 'prints
End Sub


somethinglikeant

 

[Don Guillett]

Try this instead. Change to suit your needs. Be sure to use ALL CAPS in the
case "ga" "GA"

Sub selectprintarea()
Select Case UCase(Range("a1"))
Case "GA": x = "GA"
Case "SA": x = "SA"
Case "BJ": x = "BJ"
Case "a": x = "sj"
Case Else
End Select
Range(x).PrintPreview
End Sub