Naming a range on not-active sheet

[nord7287]

Pretty simple problem (I think), but I couldn't find it anywhere on the board, so I decided to ask.

The user changes a drop down on "Top 30 Corridors", which changes some values on "Combo". Those values then are used to determine the new ranges on "OB Transactions". If I have the macro actually select the "OB Transactions" sheet, everything works fine. However, if I take that line out, then I get the application/user defined error message. It looks funny now, because when you change the dropdown, it flips you to a different sheet, and then back to the main sheet...seems like this should be an easy thing to avoid, but I don't know enough VB to troubleshoot.
Thanks for the help!

Sub rangechange()
Dim country, timeframea, timeframeb As Integer

country = Sheets("Combo").Cells(72, 3).Value
timeframea = Sheets("combo").Cells(72, 2).Value
timeframeb = Sheets("combo").Cells(72, 1).Value

x = (country - 1) * 219 + 4
y = country * 219 + 3
z = timeframea + 2
a = timeframeb + 2

Sheets("OB Transactions").Select
If country = 14 Then
Sheets("OB Transactions").Columns(z).name = "txnrange"
Sheets("OB Transactions").Columns(a).name = "oldrank"
ElseIf country < 15 Then
Sheets("OB Transactions").Range(Cells(x, z), Cells(y, z)).name = "txnrange"
Sheets("OB Transactions").Range(Cells(x, a), Cells(y, a)).name = "oldrank"
End If
Sheets("OB Transactions").Range(Cells(4, z), Cells(2849, 71)).name = "Corridor"

Sheets("Top 30 Corridors").Select
ActiveSheet.Columns("a:j").AutoFit

End Sub

 

[HalfAce]

Hello nord7287, welcome to the board.
The quickest/easiest way to solve what (I think) you're looking for would be to add a couple of lines that turn off (and then on) the screen updating. This will still allow the code to select the OB Transactions sheet to make the changes, you (or the user) just won't see it happen.
For this you would simply add the line:
Application.ScreenUpdating = False
at the top of your code (or just somewhere before the line):
Sheets("OB Transactions").Select
and then:
Application.ScreenUpdating = True
just before the end.

Is that what you're looking for?

 

[nord7287]

That does the trick...I knew it was a simple solution, but didn't think of that at all. Seems to work best if I turn the screen update back on after selecting the main sheet again. Thanks!