Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Name affecting speed of macro | Excel Worksheet Functions | |||
Identifying a Selected Range in a Macro | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Macro to change Chart Range when inserting a column | Charts and Charting in Excel | |||
Macro - define cell range for a sum function | Excel Discussion (Misc queries) |