| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Q: Specifying external Sheet name from Inputbox
Guys I think the macro better explains what Im trying to achieve... Dim CurMonth As String Sheets(Array("UK", "FR", "IT", "ES")).Select Sheets("UK").Activate ActiveCell.Offset(0, -4).Columns("A:D").EntireColumn.Select Selection.Copy ActiveCell.Offset(0, 4).Range("A1").Select Selection.Insert Shift:=xlToRight ActiveCell.Offset(0, 3).Range("A1").Select CurMonth = InputBox("Enter the Month 'Format: Month #'") ActiveCell = CurMonth ActiveCell.Offset(3, 0).Range("A1").Select ActiveCell.Value = "='[Book3]Curmonth'!B4" What I want to do is tell the macro to look in a sheet in another workbook for some data. The sheet should have the same name as in the string that I get from the Input box. How do I achieve that please? Also what property do I use if I want to change the external reference from constant (meaning with $ reference "$A$2") to variable ("A2"). Hope I make sense... -- Kayote ------------------------------------------------------------------------ Kayote's Profile: http://www.excelforum.com/member.php...o&userid=33314 View this thread: http://www.excelforum.com/showthread...hreadid=539678 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie Q: Specifying external Sheet name from Inputbox
ActiveCell.Formula = "='[Book3]" & Curmonth & "'!B4"
It already is variable. -- Regards, Tom Ogilvy "Kayote" wrote in message ... Guys I think the macro better explains what Im trying to achieve... Dim CurMonth As String Sheets(Array("UK", "FR", "IT", "ES")).Select Sheets("UK").Activate ActiveCell.Offset(0, -4).Columns("A:D").EntireColumn.Select Selection.Copy ActiveCell.Offset(0, 4).Range("A1").Select Selection.Insert Shift:=xlToRight ActiveCell.Offset(0, 3).Range("A1").Select CurMonth = InputBox("Enter the Month 'Format: Month #'") ActiveCell = CurMonth ActiveCell.Offset(3, 0).Range("A1").Select ActiveCell.Value = "='[Book3]Curmonth'!B4" What I want to do is tell the macro to look in a sheet in another workbook for some data. The sheet should have the same name as in the string that I get from the Input box. How do I achieve that please? Also what property do I use if I want to change the external reference from constant (meaning with $ reference "$A$2") to variable ("A2"). Hope I make sense... -- Kayote ------------------------------------------------------------------------ Kayote's Profile: http://www.excelforum.com/member.php...o&userid=33314 View this thread: http://www.excelforum.com/showthread...hreadid=539678 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Newbie Q: Specifying external Sheet name from Inputbox
Tom you are a star. I aspire to be as good as you! Many thanks -- Kayote ------------------------------------------------------------------------ Kayote's Profile: http://www.excelforum.com/member.php...o&userid=33314 View this thread: http://www.excelforum.com/showthread...hreadid=539678 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't Select Range off sheet from inputbox | Excel Discussion (Misc queries) | |||
| Expenses Spread sheet Tutorial for Newbie ? | Excel Discussion (Misc queries) | |||
| InputBox to capture user selected sheet names? | Excel Programming | |||
| Newbie question about accessing Excel sheet from the web | Excel Programming | |||
| Newbie Question: Returning the value from a cell on another sheet | Excel Programming | |||
Linear Mode
