|
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com