Name Range & Macro conflict
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
"John" wrote:
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
|