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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>