The ref is in several cells, but for example in cell C2 on the auxilliary
(component)sheet:

=IF('Index sheet'!D8="","",'Index Sheet'!D8)

This works fine when the auxilliary sheet is already part of the same
workbook as the index sheet - the item name will link across and appear on
the aux sheet just fine. However when I run the code to copy the aux sheet
(preset with the link above) into the same workbook as the index sheet, it
fails to make the link. I tried introducing code to refesh the code in the
cells stepwise (after the code you suggested below):

(...previous code)

Worksheets("Component Sheet").Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF('Index sheet'!D5="","",'Index Sheet'!D5)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF('Index Sheet'!D8="","",'Index Sheet'!D8)"
'and so on for all the linked cells in the aux sheet
End Sub

But this didn't work - only seemed to correct the first linked cell.

Think I may be going about this in a rather inefficient manner, maybe you
have a suggestion as to how to solve the issue and perhaps tighten the code?

Thanks again!
Gareth

"Dave Peterson" wrote:

The formula looks correct, but the value returned is "#ref!"?

And it returns the #ref error with the other workbook open?

If it only returns the #ref error when the other workbook is closed, then it
could be that your formula doesn't work across closed workbooks.

Depending on what the formula is, there may be alternatives:

=sumif() and =countif() can be replaced with =sumproduct().

But if your formulas include =indirect(), then you'll have to do more
thinking/redesigning. =indirect() doesn't work with closed workbooks.

If this isn't the problem, you may want to post the offending formula.

el_peacock wrote:

Thanks Dave, that certainly helped.
Following on from this, the worksheet I copy into my master document has
cells which need to be linked to the index page in the master doc.
This is because the rec sheet is generic and needs to reference across the
item name, item code etc from the index sheet. When I copy the sheet in, the
cells just show the #REF! remark, as if the reference is erroneous. The ref
IS correct however.
Would you have any thoughts on how to incorporate such a feature?
Cheers!
Gareth

"Dave Peterson" wrote:

I'm not sure what the names will be or when you'd call the sub, but maybe
this'll give you an idea:

Option Explicit
Sub testme()

Dim RecWksName As String
Dim RecWks As Worksheet
Dim RecWkbkName As String
Dim RecWkbk As Workbook
Dim testStr As String

RecWkbkName = "C:\my documents\excel\book99.xls"

'some way you get the names of the worksheet
RecWksName = "sheet3"

testStr = ""
On Error Resume Next
testStr = Dir(RecWkbkName)
On Error GoTo 0

If testStr = "" Then
MsgBox "design error#1--contact you at ####!"
Exit Sub
Else
Set RecWkbk = Workbooks.Open(Filename:=RecWkbkName, ReadOnly:=True)
Set RecWks = Nothing
On Error Resume Next
Set RecWks = RecWkbk.Worksheets(RecWksName)
On Error GoTo 0
If RecWks Is Nothing Then
MsgBox "design error#2--contact you at ####!"
Else
RecWks.Copy _
befo=ThisWorkbook.Worksheets(1)
End If
RecWkbk.Close savechanges:=False
End If

End Sub


el_peacock wrote:

Hi, I'll keep this as brief as poss..(!)
I've asked this question before, but nobody responded - if it's impossible,
maybe an expert could let me know?
I have a master XL document consisting of a worksheet which is essentially
an index page listing various items we stock, then each item from this list
has it's own worksheet in the same book which is a reconciliation page used
by staff to document usage/movement of the item.
At present, the way I have contructed the document is to have a checkbox on
the index sheet next to each item, and to have all reconciliation sheets
hidden. When the user wants to print the workbook, they select which items
they want a reconciliation sheet for by ticking the checkbox which unhides
the sheet.
As this document is re-used for any job we might need to use the items for,
and we have to store all documents on our system, the amount of space taken
up by these files is growing rapidly.
Is there anyway I can code the index sheet checkbox macro so that rather
than unhiding a sheet in the same workbook, it actually imports a copy of a
master reconciliation sheet from a single workbook in a fixed location for
any item for which it is required? Think this would keep file size to a
minimum.
Long winded I know, but any help would be great!!!
Thanks in advance
---------
Gareth

--

Dave Peterson


--

Dave Peterson