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

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