#REF when creating an Addin

S

stewdizzle

I am creating and addin that when opened will copy itself into an
existing workbook. From there the copied worksheet is supposed to pull
data from another worksheet and allow the user to manipulate it. I
created the worksheet in the same workbook that it will be used in.
Made sure all the formulas worked as desired and that everything was
the way i wanted it to be. However, when I copy the addin worksheet to
a new workbook to actually make it an addin I loose all my references.
How do I change the formulas so that when the worksheet is copied into
the existing workbook it references the cells properly.
 
N

Niek Otten

Post your code and the type of formulas.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am creating and addin that when opened will copy itself into an
| existing workbook. From there the copied worksheet is supposed to pull
| data from another worksheet and allow the user to manipulate it. I
| created the worksheet in the same workbook that it will be used in.
| Made sure all the formulas worked as desired and that everything was
| the way i wanted it to be. However, when I copy the addin worksheet to
| a new workbook to actually make it an addin I loose all my references.
| How do I change the formulas so that when the worksheet is copied into
| the existing workbook it references the cells properly.
|
 
S

stewdizzle

The existing workbook contains two sheets Sabourin and Jagiello. The
addin file contains a sheet called What If. The addin file contains
formulas that directly refrence the data in "Jagiello". Here is the
code that I use to copy the addin to the existing workbook:

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy
after:=ActiveWorkbook.Sheets("jagiello")
End Sub

This process works fine. It is when the sheet copies I can't get it to
refrence the sheet "Jagiello". It appears to be trying to lookback at
the addin file. Here is a sample of one of my folmulas.

=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))

#REF pops up in place of Jagiello!(cell#)

Let me know if you need anything else.

PS Sorry about the double post.
 
D

Dave Peterson

Maybe you could try:

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
application.calculate
End Sub

If that doesn't work...

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
activesheet.usedrange.replace what:="=",replacement:="=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false
End Sub

And if that doesn't work...

Sub Auto_Open()
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
application.wait now + timeserial(0,0,2) 'wait a couple of seconds
activesheet.usedrange.replace what:="=",replacement:="=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false
End Sub

All untested and all uncompiled--watch for typos.
 
D

Dave Peterson

Ignore that other post.

If you're copying a worksheet from the addin that points at a sheet in the
addin, then the copied worksheet will still point at the addin.

I'd try:

Sub Auto_Open()
with thisworkbook.sheets("what if")
'change all the formulas to text
.usedrange.replace what:="=",replacement:="$$$$$=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false
'copy it
ThisWorkbook.Sheets("What If").Copy _
after:=ActiveWorkbook.Sheets("jagiello")
'change them back
.usedrange.replace what:="$$$$$=",replacement:="=", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false

'fix the new sheet
activesheet.usedrange.replace what:="$$$$$=",replacement:="=", lookat:=xlpart,
_
searchorder:=xlbyrows, matchcase:=false
End Sub
 
N

Niek Otten

I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
Or use formulas without an equals-sign, and add that after copying and pasting, but I don't feel very comfortable with that last
solution.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| The existing workbook contains two sheets Sabourin and Jagiello. The
| addin file contains a sheet called What If. The addin file contains
| formulas that directly refrence the data in "Jagiello". Here is the
| code that I use to copy the addin to the existing workbook:
|
| Sub Auto_Open()
| ThisWorkbook.Sheets("What If").Copy
| after:=ActiveWorkbook.Sheets("jagiello")
| End Sub
|
| This process works fine. It is when the sheet copies I can't get it to
| refrence the sheet "Jagiello". It appears to be trying to lookback at
| the addin file. Here is a sample of one of my folmulas.
|
|
=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))
|
| #REF pops up in place of Jagiello!(cell#)
|
| Let me know if you need anything else.
|
| PS Sorry about the double post.
|
| > Post your code and the type of formulas.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
creating and addin that when opened will copy itself into an
| > | existing workbook. From there the copied worksheet is supposed to pull
| > | data from another worksheet and allow the user to manipulate it. I
| > | created the worksheet in the same workbook that it will be used in.
| > | Made sure all the formulas worked as desired and that everything was
| > | the way i wanted it to be. However, when I copy the addin worksheet to
| > | a new workbook to actually make it an addin I loose all my references.
| > | How do I change the formulas so that when the worksheet is copied into
| > | the existing workbook it references the cells properly.
| > |
|
 
N

Niek Otten

<someone more knowledgeable>

Of course I meant "than me"; not Dave or anyone else who responded!

Niek

|I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
| If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
| Or use formulas without an equals-sign, and add that after copying and pasting, but I don't feel very comfortable with that last
| solution.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| The existing workbook contains two sheets Sabourin and Jagiello. The
|| addin file contains a sheet called What If. The addin file contains
|| formulas that directly refrence the data in "Jagiello". Here is the
|| code that I use to copy the addin to the existing workbook:
||
|| Sub Auto_Open()
|| ThisWorkbook.Sheets("What If").Copy
|| after:=ActiveWorkbook.Sheets("jagiello")
|| End Sub
||
|| This process works fine. It is when the sheet copies I can't get it to
|| refrence the sheet "Jagiello". It appears to be trying to lookback at
|| the addin file. Here is a sample of one of my folmulas.
||
||
|
=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))
||
|| #REF pops up in place of Jagiello!(cell#)
||
|| Let me know if you need anything else.
||
|| PS Sorry about the double post.
||
|| > Post your code and the type of formulas.
|| >
|| > --
|| > Kind regards,
|| >
|| > Niek Otten
|| > Microsoft MVP - Excel
|| >
| creating and addin that when opened will copy itself into an
|| > | existing workbook. From there the copied worksheet is supposed to pull
|| > | data from another worksheet and allow the user to manipulate it. I
|| > | created the worksheet in the same workbook that it will be used in.
|| > | Made sure all the formulas worked as desired and that everything was
|| > | the way i wanted it to be. However, when I copy the addin worksheet to
|| > | a new workbook to actually make it an addin I loose all my references.
|| > | How do I change the formulas so that when the worksheet is copied into
|| > | the existing workbook it references the cells properly.
|| > |
||
|
|
 
D

Dave Peterson

I like a variation of the last one.

<bg>

Niek said:
I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
Or use formulas without an equals-sign, and add that after copying and pasting, but I don't feel very comfortable with that last
solution.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| The existing workbook contains two sheets Sabourin and Jagiello. The
| addin file contains a sheet called What If. The addin file contains
| formulas that directly refrence the data in "Jagiello". Here is the
| code that I use to copy the addin to the existing workbook:
|
| Sub Auto_Open()
| ThisWorkbook.Sheets("What If").Copy
| after:=ActiveWorkbook.Sheets("jagiello")
| End Sub
|
| This process works fine. It is when the sheet copies I can't get it to
| refrence the sheet "Jagiello". It appears to be trying to lookback at
| the addin file. Here is a sample of one of my folmulas.
|
|
=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))
|
| #REF pops up in place of Jagiello!(cell#)
|
| Let me know if you need anything else.
|
| PS Sorry about the double post.
|
| > Post your code and the type of formulas.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
creating and addin that when opened will copy itself into an
| > | existing workbook. From there the copied worksheet is supposed to pull
| > | data from another worksheet and allow the user to manipulate it. I
| > | created the worksheet in the same workbook that it will be used in.
| > | Made sure all the formulas worked as desired and that everything was
| > | the way i wanted it to be. However, when I copy the addin worksheet to
| > | a new workbook to actually make it an addin I loose all my references.
| > | How do I change the formulas so that when the worksheet is copied into
| > | the existing workbook it references the cells properly.
| > |
|
Click to expand...
 
S

stewdizzle

Dave you are a lifesaver. I don't know why I didn't try this earlier.
I was doing it manually to see if it would work just couldn't tie up
the loose ends. For reference Here is the final code.

Sub Auto_Open()
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("What If")
'copy it
ThisWorkbook.Sheets("What If").Copy
after:=ActiveWorkbook.Sheets("Jagiello")
'fix the new sheet
Sheets("what if").Cells.Replace what:="$$$", replacement:="=",
lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Application.ScreenUpdating = True
End Sub

I just saved my addin with "$$$" in the place of "=" And eliminated the
step of changing it and then fixing it.

In short, thank you Dave and Niek for the help.
 
D

Dave Peterson

That's the way I do it, too (save the formulas as text).

(I do use $$$$$=, though. It's just a bit more unique <vbg>.)
 
N

Niek Otten

<I like a variation of the last one.>

OK!
I'm not sure I can oversee all the implications, like opening in a version of Excel with other list separators, R1C1 reference
style, etc. As long as they're recognized as formulas, that will be taken care of automatically, but with text, no. I even don't
know what happens in another language version. I do with worksheets, but I don't with worksheets copied from an add-in.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I like a variation of the last one.
|
| <bg>
|
| Niek Otten wrote:
| >
| > I hope someone more knowledgeable will answer, but it does indeed look like "looking back" to the add-in.
| > If that is the case, you might try using a cell with the worksheet name and use the INDIRECT() function in the formula.
| > Or use formulas without an equals-sign, and add that after copying and pasting, but I don't feel very comfortable with that
last
| > solution.
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | The existing workbook contains two sheets Sabourin and Jagiello. The
| > | addin file contains a sheet called What If. The addin file contains
| > | formulas that directly refrence the data in "Jagiello". Here is the
| > | code that I use to copy the addin to the existing workbook:
| > |
| > | Sub Auto_Open()
| > | ThisWorkbook.Sheets("What If").Copy
| > | after:=ActiveWorkbook.Sheets("jagiello")
| > | End Sub
| > |
| > | This process works fine. It is when the sheet copies I can't get it to
| > | refrence the sheet "Jagiello". It appears to be trying to lookback at
| > | the addin file. Here is a sample of one of my folmulas.
| > |
| > |
| >
=IF($A$6=Jagiello!$A$10,Jagiello!C10,IF($A$6=Jagiello!$A$9,Jagiello!C9,IF($A$6=Jagiello!$A$8,Jagiello!C8,IF($A$6=Jagiello!$A$7,Jagiello!C7,IF($A$6=Jagiello!$A$6,Jagiello!C6,0)))))
| > |
| > | #REF pops up in place of Jagiello!(cell#)
| > |
| > | Let me know if you need anything else.
| > |
| > | PS Sorry about the double post.
| > |
| > | > Post your code and the type of formulas.
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > creating and addin that when opened will copy itself into an
| > | > | existing workbook. From there the copied worksheet is supposed to pull
| > | > | data from another worksheet and allow the user to manipulate it. I
| > | > | created the worksheet in the same workbook that it will be used in.
| > | > | Made sure all the formulas worked as desired and that everything was
| > | > | the way i wanted it to be. However, when I copy the addin worksheet to
| > | > | a new workbook to actually make it an addin I loose all my references.
| > | > | How do I change the formulas so that when the worksheet is copied into
| > | > | the existing workbook it references the cells properly.
| > | > |
| > |
|
| --
|
| Dave Peterson
 
D

Dave Peterson

I haven't done any testing on this, but I would think that if the formulas are
kept as formulas, then converted to text, then back to formulas (not what the OP
ended up doing), I don't think that the R1C1/list separators would cause a
problem (since each workbook would be using excel's application settings).

But it does make for a good reason to keep the original formulas as formulas and
do that extra couple of steps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to transfer a worksheet into a new workbook and keep the formulas intact 5
COM addin won't connect when workbook opened via another workbookfrom another instance 3
About references 1
Paste link only - not value 4
Copy a worksheet to a new workbook 6
Copy a Sheet to an AddIn 2
copying cells with formulas, numbers, & text 6
Worksheet management 2

Top