I was told that I can copy an existing worksheet and create links to that
worksheet automatically. Does anyone know how to do this?

I was told that I can copy an existing worksheet and create links to
that
worksheet automatically. Does anyone know how to do this?

Need more information about what you are actually trying to do.

You can use VBA to create a copy of a worksheet in the same workbook,
but you can't have two worksheets with the same name. Therefore any
links to the first worksheet will be 'broken' for for the copy because
the new worksheet will have a new name.

You can also use VBA to copy a worksheet to another workbook, and the
worksheet can, in this case, have the same name. However, since it
would now be in a different workbook, any links to the worksheet would
be broken.

In either of the above cases, VBA can be used to write formulas to a
third worksheet and those formulas can reference the new copy of the
worksheet (either with its new name in its original workbook, or with
whatever workbook and worksheet name it was copied to), but it's not
clear if that is what you meant by 'automatically' and whether you
really meant links _to_ or _from__the copy...

There are some real wizards here (and I'm not one of them) but even
they would need more specifics about what you need to happen.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111463

Thank you for your response. I'm sorry I was not more specific. I actually
need to create 10 new worksheets in 10 separate workbooks and have each of
them link back to the original. I'm not sure how this would be possible, but
I was told it can happen. The person who told me this though cannot remember
how it is done.

"jamescox" wrote:


I was told that I can copy an existing worksheet and create links to
that
worksheet automatically. Does anyone know how to do this?

Need more information about what you are actually trying to do.

You can use VBA to create a copy of a worksheet in the same workbook,
but you can't have two worksheets with the same name. Therefore any
links to the first worksheet will be 'broken' for for the copy because
the new worksheet will have a new name.

You can also use VBA to copy a worksheet to another workbook, and the
worksheet can, in this case, have the same name. However, since it
would now be in a different workbook, any links to the worksheet would
be broken.

In either of the above cases, VBA can be used to write formulas to a
third worksheet and those formulas can reference the new copy of the
worksheet (either with its new name in its original workbook, or with
whatever workbook and worksheet name it was copied to), but it's not
clear if that is what you meant by 'automatically' and whether you
really meant links _to_ or _from__the copy...

There are some real wizards here (and I'm not one of them) but even
they would need more specifics about what you need to happen.


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111463

I'm still not clear on what the significance for you is of the phrase
"have each of them link back to the original."

However, to create in Workbook B a copy of a worksheet Fred that's in
Workbook A

1. Open both Workbook A and Workbook B in the same Excel session

2. Right click on the tab of worksheet Fred

3. Select the Move or Copy item from the pop-up menu

4. In the Move selected sheets to book box, click on the dropdown arrow
and select Workbook B

5. In the Before sheet: box, select where you want the copy of
worksheet Fred to appear in the order of worksheets in Workbook B

6. Check the Create a copy checkbox << VERY IMPORTANT not to forget
this!

7. Click the OK button


In terms of links, what this will is translate any cell references to
other worksheets in Workbook A that are on worksheet Fred (normally in
the format Sheet2!D7 or 'Monthy Sales'!G13 if the worksheet name as an
embedded blank in it) to a reference that includes the name of Workbook
A ('[Workbook A.xlsm]Sheet1'!$E$8). Perhaps it's this 'link' that has
been established between the version of worksheet Fred that is in
Workbook B back to cells in Workbook A that you are referring to.

At any rate, that's how to create a copy of a worksheet and move it to
other workbooks...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111463