Linking worksheets across workbooks
 

I would like to try to link a worksheet or tab in a workbook to an external
excel file. I know that we can link cells that reference other workbooks,
but is it possilbe that just the entire tab could reference another worksheet
from an outside workbook?

-steve

You'll need to do it cell by cell.

But once you create the formula for A1 (and change it to relative addressing
(losing the $ signs in the address), you can copy down and copy across.


Steven M. Britton wrote:

I would like to try to link a worksheet or tab in a workbook to an external
excel file. I know that we can link cells that reference other workbooks,
but is it possilbe that just the entire tab could reference another worksheet
from an outside workbook?

-steve

--

Dave Peterson

Understand how to adjust the absolute, but what if I have my cell by cell
going from A1:J358. Now is someone adds a row to the external sheet say they
add a total or summary starting in A375, how would I know in my Main linked
sheet? Do I need to just have the entire sheet reference the other? If so
how large is that going to make the file and would it ruin performance?

"Dave Peterson" wrote:

You'll need to do it cell by cell.

But once you create the formula for A1 (and change it to relative addressing
(losing the $ signs in the address), you can copy down and copy across.


Steven M. Britton wrote:

I would like to try to link a worksheet or tab in a workbook to an external
excel file. I know that we can link cells that reference other workbooks,
but is it possilbe that just the entire tab could reference another worksheet
from an outside workbook?

-steve

--

Dave Peterson

First, I wouldn't do what you want--exactly for the reasons you've stated.

I'd open that other workbook when I needed to refer to it.

Or....

Maybe you could retrieve a copy of that worksheet whenever you opened the
workbook. (It does sound like you're just using it for reference.)

Option Explicit
Sub auto_open()

Dim myFileName As String
Dim myWksName As String

Dim wkbk As Workbook
Dim wks As Worksheet
Dim testStr As String

myFileName = "C:\my documents\excel\book1.xls"
myWksName = "Sheet1"

testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0
If testStr = "" Then
MsgBox myFileName & " not found!"
Exit Sub
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wkbk = Workbooks.Open(Filename:=myFileName, UpdateLinks:=0,
ReadOnly:=True)
Set wks = Nothing
On Error Resume Next
Set wks = wkbk.Worksheets(myWksName)
On Error GoTo 0
If wks Is Nothing Then
MsgBox myWksName & " wasn't found in: " & myFileName
Else
With wks
With .UsedRange
.Value = .Value
End With
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(myWksName).Delete
Application.DisplayAlerts = True
On Error GoTo 0

.Copy _
befo=ThisWorkbook.Worksheets(1)
End With
End If

wkbk.Close savechanges:=False

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Steven M. Britton wrote:

Understand how to adjust the absolute, but what if I have my cell by cell
going from A1:J358. Now is someone adds a row to the external sheet say they
add a total or summary starting in A375, how would I know in my Main linked
sheet? Do I need to just have the entire sheet reference the other? If so
how large is that going to make the file and would it ruin performance?

"Dave Peterson" wrote:

You'll need to do it cell by cell.

But once you create the formula for A1 (and change it to relative addressing
(losing the $ signs in the address), you can copy down and copy across.


Steven M. Britton wrote:

I would like to try to link a worksheet or tab in a workbook to an external
excel file. I know that we can link cells that reference other workbooks,
but is it possilbe that just the entire tab could reference another worksheet
from an outside workbook?

-steve

--

Dave Peterson


--

Dave Peterson