Name Range & Macro conflict
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 |
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 |
Name Range & Macro conflict
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 -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... 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 -- Don Guillett SalesAid Software "John" wrote in message ... 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 |
All times are GMT +1. The time now is 02:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com