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

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

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

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

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

I'm not sure how you're copying the worksheet, so I don't have a guess.

But I think this:

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)"

should look more like:
with Worksheets("Component Sheet")
.Range("C2").Formula = "=IF('Index sheet'!D5="""","""",'Index Sheet'!D5)"
.Range("D2").Formula = "=IF('Index Sheet'!D8="""","""",'Index Sheet'!D8)"
end with

notice that there's no selection, .formular1c1 is replaced with .formula and the
quotation marks have been doubled up.

====
Do you copy the whole sheet (like ctrl-dragging the worksheet tab manually) or
do you select the cells and copy them? Or do you move them???




el_peacock wrote:

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


--

Dave Peterson

Dave, that worked fine thanks. In answer to your question, I copy the sheet
in using the copy method you suggested at the beginning of this thread! So
now the code runs as per your copy-in method followed by the formula being
added in.

Because there are 10 items on the index sheet, I have copied this code for
each of the items and applied it to a checkbox (i.e. when the user sets the
checkbox for an item to true, a reconciliation sheet is copied in from the
auxiliary sheet, then the formulas which copy the item information from the
index sheet are added to the rec sheet). This works well and I'm quite
pleased with it (thanks fo your help), only thing is the code has become
lengthy because I've just repeated it character for characer for each of the
10 items on the index sheet. Going to work on creating a generic code which
could apply to all 10 checkboxes, may come back if I need some assistance,
hope that's ok.

Cheers
Gareth

"Dave Peterson" wrote:

I'm not sure how you're copying the worksheet, so I don't have a guess.

But I think this:

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)"

should look more like:
with Worksheets("Component Sheet")
.Range("C2").Formula = "=IF('Index sheet'!D5="""","""",'Index Sheet'!D5)"
.Range("D2").Formula = "=IF('Index Sheet'!D8="""","""",'Index Sheet'!D8)"
end with

notice that there's no selection, .formular1c1 is replaced with .formula and the
quotation marks have been doubled up.

====
Do you copy the whole sheet (like ctrl-dragging the worksheet tab manually) or
do you select the cells and copy them? Or do you move them???




el_peacock wrote:

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


--

Dave Peterson