Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Variable to Reference Workbooks
Can you use a variable to reference a cell in a different workbook? I'm trying to compile data from a number of different workbooks in a "master" workbook. The MACRO that I'm writing prompts the user to enter the name of a company which is also name of another excel spreadsheet containing data for that specific company. Therefore, I have defined a variable to take on the name of each company and I'm trying to use this variable to call specific cells in the company different workbooks. Any help would be greatly appreciated. See the code below: Sub Linking() Dim ReplaceName As String Dim Name As String Dim CountNumCo As Integer Dim Num As Integer Num = InputBox("Enter the nunber of portfolio companies: ") Range("IV65536") = Name For CountNumCo = 1 To Num Name = InputBox("Enter the name of the portfolio company: ") Range("IV65536").Offset(-(CountNumCo - 1), 0) = Name Next CountNumCo ReplaceName = Range("IV65536") & ".xls" Range("A1").Select ActiveCell.FormulaR1C1 = _ "='[ReplaceName]Other Coverages'!R1C1" Range("B2").Select ActiveCell.FormulaR1C1 = _ "='[ReplaceName]Casualty Income'!R7C7" Range("B3").Select ActiveCell.FormulaR1C1 = _ "='[ReplaceName]Other Coverages'!R7C9" Range("B4").Select End Sub -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=379104 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Variable to Reference Workbooks
In short, the answer is yes, but don't use Name as a variable, use something
like sName or myName. The trick is to separate the variable and the string text and concatenate them (if that double-Dutch makes sense<g) Range("A1").FormulaR1C1 = _ "='[" & sName & "]Other Coverages'!R1C1" -- HTH Bob Phillips "PGalla06" wrote in message ... Can you use a variable to reference a cell in a different workbook? I'm trying to compile data from a number of different workbooks in a "master" workbook. The MACRO that I'm writing prompts the user to enter the name of a company which is also name of another excel spreadsheet containing data for that specific company. Therefore, I have defined a variable to take on the name of each company and I'm trying to use this variable to call specific cells in the company different workbooks. Any help would be greatly appreciated. See the code below: Sub Linking() Dim ReplaceName As String Dim Name As String Dim CountNumCo As Integer Dim Num As Integer Num = InputBox("Enter the nunber of portfolio companies: ") Range("IV65536") = Name For CountNumCo = 1 To Num Name = InputBox("Enter the name of the portfolio company: ") Range("IV65536").Offset(-(CountNumCo - 1), 0) = Name Next CountNumCo ReplaceName = Range("IV65536") & ".xls" Range("A1").Select ActiveCell.FormulaR1C1 = _ "='[ReplaceName]Other Coverages'!R1C1" Range("B2").Select ActiveCell.FormulaR1C1 = _ "='[ReplaceName]Casualty Income'!R7C7" Range("B3").Select ActiveCell.FormulaR1C1 = _ "='[ReplaceName]Other Coverages'!R7C9" Range("B4").Select End Sub -- PGalla06 ------------------------------------------------------------------------ PGalla06's Profile: http://www.excelforum.com/member.php...o&userid=24260 View this thread: http://www.excelforum.com/showthread...hreadid=379104 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Link to Workbooks | Excel Worksheet Functions | |||
Workbooks with variable names | Excel Programming | |||
Use variable across workbooks | Excel Programming | |||
Passing a variable between workbooks | Excel Programming | |||
How to refer a Variable across Workbooks ? | Excel Programming |