Name Range & Macro conflict

[Guest]

Hi,

I have a couple of sheets, one with a week on week budget, the other I want
to pull the budget data on.
I've named each weeks budget and tried to use the macro:

Dim Week, budgets
Week = InputBox("What budget week do you want to display?", "Budget Week")



Select Case Week
Case Is = 1
budgets = Sheets("Budget").Select
Application.Goto Reference:="WK1"

Case Is = 2
budgets = Sheets("Budget").Select
Application.Goto Reference:="WK2"





Case Else
Area = "but you don't seem to know your Budget week"

End Select


Range("I38").Select

Application.Goto Reference:=Worksheets("Budget").Range("Budgets"), _
scroll:=True

Selection.Copy
Sheets("Summary by Plant").Select
Range("I38").Select
ActiveSheet.Paste

Can't seem to find the right code to pull in the named range.

Any ideas please?

J

 

[Don Guillett]

Easier than you think

Sub selectweek()
week = InputBox("What budget week do you want to display?", "Budget Week")
Application.Goto Range("wk" & week)
End Sub

 

[Guest]

I am guessing that the budgets are named Wk1, Wk2 (? ) so if so try this. I
commented out code which I think can be removed.

Sub a()
Dim Week, budgets
Week = InputBox("What budget week do you want to display?", "Budget Week")
If Week >= "1" And Week <= "52" Then
Set rng = Sheets("Budget").Range("Wk" & Week)
Else
MsgBox "but you don't seem to know your Budget week"
' .......<=== whatever
End If


'Select Case Week
'Case Is = 1
'budgets = Sheets("Budget").Select
' Application.Goto Reference:="WK1"
'
'Case Is = 2
'budgets = Sheets("Budget").Select
' Application.Goto Reference:="WK2"
'
'
'
'
'
'Case Else
'Area = "but you don't seem to know your Budget week"
'
'End Select


Range("I38").Select

' Application.Goto Reference:=Worksheets("Budget").Range("Budgets"), _
' scroll:=True

Application.Goto Reference:=rng, scroll:=True

Selection.Copy
Sheets("Summary by Plant").Select
Range("I38").Select
ActiveSheet.Paste


End Sub

 

[Don Guillett]

error trap

Sub selectweek()
On Error GoTo nowk
week = InputBox("What budget week do you want to display?", "Budget Week")
Application.Goto Range("wk" & week)
Exit Sub
nowk:
MsgBox "you no know"
End Sub