Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it contains all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap, payroll, and
details). These were put in the addin file...inorder to run code that
copies these worksheets and adds them to the activeworkbook incase the users
needs more of those sheets. If one sheet is added they all three added
.....as they work together.

So... after a one set of the worksheets have been added.. the workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I now have
formulas that are in the Recap sheet that link to the Details sheet and I
need to add the formula to the added recap sheets...when it is added and
have it correspond to the added Details sheet that works with it...

Is there a way to add a formula to the added Recap sheet that will reference
the added Details sheet .....keep in mind I won't know the number of the
sheets added...as the user can add as many sets as needed. They are added
is sets ... which I think helps with running the code to do this..

The formula needs to go into cell D8 of the Recap sheets added and is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that has been
added ( for ex...Recap (2) ) and have code set cell D8 of the Recap (2) to
=Details (2)!G7 and so on... so when a thrid set of sheets are added... the
sheets would be Recap (3), Payroll (3), and Details (3)...and the formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

Kimberly,
I just opened a new wbk, named the three sheets, entered a formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not, try this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it contains all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap, payroll, and
details). These were put in the addin file...inorder to run code that
copies these worksheets and adds them to the activeworkbook incase the users
needs more of those sheets. If one sheet is added they all three added
.....as they work together.

So... after a one set of the worksheets have been added.. the workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I now have
formulas that are in the Recap sheet that link to the Details sheet and I
need to add the formula to the added recap sheets...when it is added and
have it correspond to the added Details sheet that works with it...

Is there a way to add a formula to the added Recap sheet that will reference
the added Details sheet .....keep in mind I won't know the number of the
sheets added...as the user can add as many sets as needed. They are added
is sets ... which I think helps with running the code to do this..

The formula needs to go into cell D8 of the Recap sheets added and is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that has been
added ( for ex...Recap (2) ) and have code set cell D8 of the Recap (2) to
=Details (2)!G7 and so on... so when a thrid set of sheets are added... the
sheets would be Recap (3), Payroll (3), and Details (3)...and the formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the workbook. I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not, try this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap, payroll, and
details). These were put in the addin file...inorder to run code that
copies these worksheets and adds them to the activeworkbook incase the
users
needs more of those sheets. If one sheet is added they all three added
.....as they work together.

So... after a one set of the worksheets have been added.. the workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I now have
formulas that are in the Recap sheet that link to the Details sheet and
I
need to add the formula to the added recap sheets...when it is added and
have it correspond to the added Details sheet that works with it...

Is there a way to add a formula to the added Recap sheet that will
reference
the added Details sheet .....keep in mind I won't know the number of the
sheets added...as the user can add as many sets as needed. They are
added
is sets ... which I think helps with running the code to do this..

The formula needs to go into cell D8 of the Recap sheets added and is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that has been
added ( for ex...Recap (2) ) and have code set cell D8 of the Recap (2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets are added...
the
sheets would be Recap (3), Payroll (3), and Details (3)...and the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

Turn on your macro recorder and do the same steps I followed above except in
the Move or Copy Sheet dialog, select (move to end) in the Where section of
the dialog.
After stopping the recorder open your VBE and look at the code. The portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the workbook. I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not, try this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap, payroll, and
details). These were put in the addin file...inorder to run code that
copies these worksheets and adds them to the activeworkbook incase the
users
needs more of those sheets. If one sheet is added they all three added
.....as they work together.

So... after a one set of the worksheets have been added.. the workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I now have
formulas that are in the Recap sheet that link to the Details sheet and
I
need to add the formula to the added recap sheets...when it is added and
have it correspond to the added Details sheet that works with it...

Is there a way to add a formula to the added Recap sheet that will
reference
the added Details sheet .....keep in mind I won't know the number of the
sheets added...as the user can add as many sets as needed. They are
added
is sets ... which I think helps with running the code to do this..

The formula needs to go into cell D8 of the Recap sheets added and is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that has been
added ( for ex...Recap (2) ) and have code set cell D8 of the Recap (2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets are added...
the
sheets would be Recap (3), Payroll (3), and Details (3)...and the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed above except
in
the Move or Copy Sheet dialog, select (move to end) in the Where section
of
the dialog.
After stopping the recorder open your VBE and look at the code. The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not, try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it
contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap, payroll,
and
details). These were put in the addin file...inorder to run code
that
copies these worksheets and adds them to the activeworkbook incase
the
users
needs more of those sheets. If one sheet is added they all three
added
.....as they work together.

So... after a one set of the worksheets have been added.. the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I now
have
formulas that are in the Recap sheet that link to the Details sheet
and
I
need to add the formula to the added recap sheets...when it is added
and
have it correspond to the added Details sheet that works with it...

Is there a way to add a formula to the added Recap sheet that will
reference
the added Details sheet .....keep in mind I won't know the number of
the
sheets added...as the user can add as many sets as needed. They are
added
is sets ... which I think helps with running the code to do this..

The formula needs to go into cell D8 of the Recap sheets added and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that has
been
added ( for ex...Recap (2) ) and have code set cell D8 of the Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets are
added...
the
sheets would be Recap (3), Payroll (3), and Details (3)...and the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

--
Regards,
Tom Ogilvy


"KimberlyC" wrote in message
...
I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed above
except
in
the Move or Copy Sheet dialog, select (move to end) in the Where section
of
the dialog.
After stopping the recorder open your VBE and look at the code. The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the
workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not, try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it
contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap,
payroll,
and
details). These were put in the addin file...inorder to run code
that
copies these worksheets and adds them to the activeworkbook incase
the
users
needs more of those sheets. If one sheet is added they all three
added
.....as they work together.

So... after a one set of the worksheets have been added.. the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I now
have
formulas that are in the Recap sheet that link to the Details
sheet
and
I
need to add the formula to the added recap sheets...when it is
added
and
have it correspond to the added Details sheet that works with
it...

Is there a way to add a formula to the added Recap sheet that will
reference
the added Details sheet .....keep in mind I won't know the number
of
the
sheets added...as the user can add as many sets as needed. They
are
added
is sets ... which I think helps with running the code to do this..

The formula needs to go into cell D8 of the Recap sheets added and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that
has
been
added ( for ex...Recap (2) ) and have code set cell D8 of the
Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets are
added...
the
sheets would be Recap (3), Payroll (3), and Details (3)...and the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

I have another question...that just popped up...

When I'm adding these worksheets from the addin file to the Active
Workbook... a cell in worksheet RECAP located in the addin file has a
formula that references a named range in the active workbook.
For example: Recap B1 (in the addin file) has the following formula =Fein
( where Fein is a named range in a cell in the active workbook )

The formula appears as #REF! in the addin file.. (as there is no Fein Range
in the addin file which I understand).

So when I add the worksheets.... I get the following message:

"A formula or sheet you want to move or copy contains the name 'fein', which
already exists on the destination worksheet. Do you want to use this version
of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formual or worksheet, click No, and
enter a new name in the Name Confilict dialog box."

When I click yes... everything transfers over fine .... however...my
question is ... Is there a way to bypass this message so the users do not
have to be faced with it when adding the worksheets?

Thanks for you help!




"Tom Ogilvy" wrote in message
...
After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

--
Regards,
Tom Ogilvy


"KimberlyC" wrote in message
...
I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed above
except
in
the Move or Copy Sheet dialog, select (move to end) in the Where
section
of
the dialog.
After stopping the recorder open your VBE and look at the code. The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the
workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy
Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not,
try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it
contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap,
payroll,
and
details). These were put in the addin file...inorder to run
code
that
copies these worksheets and adds them to the activeworkbook
incase
the
users
needs more of those sheets. If one sheet is added they all
three
added
.....as they work together.

So... after a one set of the worksheets have been added.. the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I
now
have
formulas that are in the Recap sheet that link to the Details
sheet
and
I
need to add the formula to the added recap sheets...when it is
added
and
have it correspond to the added Details sheet that works with
it...

Is there a way to add a formula to the added Recap sheet that
will
reference
the added Details sheet .....keep in mind I won't know the
number
of
the
sheets added...as the user can add as many sets as needed. They
are
added
is sets ... which I think helps with running the code to do
this..

The formula needs to go into cell D8 of the Recap sheets added
and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number that
has
been
added ( for ex...Recap (2) ) and have code set cell D8 of the
Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets are
added...
the
sheets would be Recap (3), Payroll (3), and Details (3)...and
the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

In the addin make the formula (add a single quote at the beginning)
'=fein

then after you copy the sheet correct the formula with

activesheet.Range("A1").Formula = activesheet.Range("A1").Value

--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
I have another question...that just popped up...

When I'm adding these worksheets from the addin file to the Active
Workbook... a cell in worksheet RECAP located in the addin file has a
formula that references a named range in the active workbook.
For example: Recap B1 (in the addin file) has the following formula =Fein
( where Fein is a named range in a cell in the active workbook )

The formula appears as #REF! in the addin file.. (as there is no Fein
Range
in the addin file which I understand).

So when I add the worksheets.... I get the following message:

"A formula or sheet you want to move or copy contains the name 'fein',
which
already exists on the destination worksheet. Do you want to use this
version
of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formual or worksheet, click No,
and
enter a new name in the Name Confilict dialog box."

When I click yes... everything transfers over fine .... however...my
question is ... Is there a way to bypass this message so the users do not
have to be faced with it when adding the worksheets?

Thanks for you help!




"Tom Ogilvy" wrote in message
...
After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

--
Regards,
Tom Ogilvy


"KimberlyC" wrote in message
...
I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed above
except
in
the Move or Copy Sheet dialog, select (move to end) in the Where
section
of
the dialog.
After stopping the recorder open your VBE and look at the code. The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the
workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a
formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy
Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If not,
try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and it
contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap,
payroll,
and
details). These were put in the addin file...inorder to run
code
that
copies these worksheets and adds them to the activeworkbook
incase
the
users
needs more of those sheets. If one sheet is added they all
three
added
.....as they work together.

So... after a one set of the worksheets have been added.. the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it won't
go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however.. I
now
have
formulas that are in the Recap sheet that link to the Details
sheet
and
I
need to add the formula to the added recap sheets...when it is
added
and
have it correspond to the added Details sheet that works with
it...

Is there a way to add a formula to the added Recap sheet that
will
reference
the added Details sheet .....keep in mind I won't know the
number
of
the
sheets added...as the user can add as many sets as needed.
They
are
added
is sets ... which I think helps with running the code to do
this..

The formula needs to go into cell D8 of the Recap sheets added
and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number
that
has
been
added ( for ex...Recap (2) ) and have code set cell D8 of the
Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets
are
added...
the
sheets would be Recap (3), Payroll (3), and Details (3)...and
the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

Thanks!
Since I am adding three sheets at one time (from the addin file to the
activeworkbook) and each of these sheets have the =fein formula in cell
A1....how will I be able to correct the formula in cell A1on each worksheet
after adding them??
Also.. I will not know the exact name of the worksheets added as a number is
added to the name depending on how many times the user has added them.
For example... the active workbook has the following three worksheets in the
template:
Recap, Payroll, and Detials

When the user adds more worksheets.. (which they all three get added at one
time with this code):
Workbooks("TestAddin3.xls").Sheets(Array("Recap", "Payroll",
"Details")).Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
the active workbook now has the following worksheets:
Recap, Payroll, Details, Recap (2), Payroll (2), Detials (2)

And.. when the users adds another set...the active workbook appears with
these worksheets:
Recap, Payroll, Details, Recap (2), Payroll (2), Detials (2), Recap (3),
Payroll (3), Details (3)
and so on as more are added.

So.. I'm not sure how I would locate the worksheets that were just added (by
the user).....and then correct the formula in cell A1 of all three sheets.

Thanks so much for your help!!
I really appreicate it..
Kimberly


"Tom Ogilvy" wrote in message
...
In the addin make the formula (add a single quote at the beginning)
'=fein

then after you copy the sheet correct the formula with

activesheet.Range("A1").Formula = activesheet.Range("A1").Value

--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
I have another question...that just popped up...

When I'm adding these worksheets from the addin file to the Active
Workbook... a cell in worksheet RECAP located in the addin file has a
formula that references a named range in the active workbook.
For example: Recap B1 (in the addin file) has the following formula
=Fein
( where Fein is a named range in a cell in the active workbook )

The formula appears as #REF! in the addin file.. (as there is no Fein
Range
in the addin file which I understand).

So when I add the worksheets.... I get the following message:

"A formula or sheet you want to move or copy contains the name 'fein',
which
already exists on the destination worksheet. Do you want to use this
version
of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formual or worksheet, click No,
and
enter a new name in the Name Confilict dialog box."

When I click yes... everything transfers over fine .... however...my
question is ... Is there a way to bypass this message so the users do
not
have to be faced with it when adding the worksheets?

Thanks for you help!




"Tom Ogilvy" wrote in message
...
After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

--
Regards,
Tom Ogilvy


"KimberlyC" wrote in message
...
I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed above
except
in
the Move or Copy Sheet dialog, select (move to end) in the Where
section
of
the dialog.
After stopping the recorder open your VBE and look at the code.
The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the
workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a
formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy
Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed to
=Detail (2)!G7

Does your addin use the same code to create the copies? If
not,
try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook and
it
contains
all
the codes to operate the buttons and menus on the workbook.

In the addin file I have the same three worksheets (recap,
payroll,
and
details). These were put in the addin file...inorder to run
code
that
copies these worksheets and adds them to the activeworkbook
incase
the
users
needs more of those sheets. If one sheet is added they all
three
added
.....as they work together.

So... after a one set of the worksheets have been added..
the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details (2)

They can added as many sets as needed....my guess is it
won't
go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine...... however..
I
now
have
formulas that are in the Recap sheet that link to the
Details
sheet
and
I
need to add the formula to the added recap sheets...when it
is
added
and
have it correspond to the added Details sheet that works
with
it...

Is there a way to add a formula to the added Recap sheet
that
will
reference
the added Details sheet .....keep in mind I won't know the
number
of
the
sheets added...as the user can add as many sets as needed.
They
are
added
is sets ... which I think helps with running the code to do
this..

The formula needs to go into cell D8 of the Recap sheets
added
and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number
that
has
been
added ( for ex...Recap (2) ) and have code set cell D8 of
the
Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of sheets
are
added...
the
sheets would be Recap (3), Payroll (3), and Details
(3)...and
the
formula
needed in Recap (3) cell D8 would need to be =Details (3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

With Activeworkbook
for i = .worksheets.count to .worksheets.count - 2 step -1
With Worksheets(i)
.Range("A1").Formula = .range("A1").Value
End with
Next
End With


--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
Thanks!
Since I am adding three sheets at one time (from the addin file to the
activeworkbook) and each of these sheets have the =fein formula in cell
A1....how will I be able to correct the formula in cell A1on each
worksheet
after adding them??
Also.. I will not know the exact name of the worksheets added as a number
is
added to the name depending on how many times the user has added them.
For example... the active workbook has the following three worksheets in
the
template:
Recap, Payroll, and Detials

When the user adds more worksheets.. (which they all three get added at
one
time with this code):
Workbooks("TestAddin3.xls").Sheets(Array("Recap", "Payroll",
"Details")).Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
the active workbook now has the following worksheets:
Recap, Payroll, Details, Recap (2), Payroll (2), Detials (2)

And.. when the users adds another set...the active workbook appears with
these worksheets:
Recap, Payroll, Details, Recap (2), Payroll (2), Detials (2), Recap (3),
Payroll (3), Details (3)
and so on as more are added.

So.. I'm not sure how I would locate the worksheets that were just added
(by
the user).....and then correct the formula in cell A1 of all three sheets.

Thanks so much for your help!!
I really appreicate it..
Kimberly


"Tom Ogilvy" wrote in message
...
In the addin make the formula (add a single quote at the beginning)
'=fein

then after you copy the sheet correct the formula with

activesheet.Range("A1").Formula = activesheet.Range("A1").Value

--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
I have another question...that just popped up...

When I'm adding these worksheets from the addin file to the Active
Workbook... a cell in worksheet RECAP located in the addin file has a
formula that references a named range in the active workbook.
For example: Recap B1 (in the addin file) has the following formula
=Fein
( where Fein is a named range in a cell in the active workbook )

The formula appears as #REF! in the addin file.. (as there is no Fein
Range
in the addin file which I understand).

So when I add the worksheets.... I get the following message:

"A formula or sheet you want to move or copy contains the name 'fein',
which
already exists on the destination worksheet. Do you want to use this
version
of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formual or worksheet, click
No,
and
enter a new name in the Name Confilict dialog box."

When I click yes... everything transfers over fine .... however...my
question is ... Is there a way to bypass this message so the users do
not
have to be faced with it when adding the worksheets?

Thanks for you help!




"Tom Ogilvy" wrote in message
...
After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

--
Regards,
Tom Ogilvy


"KimberlyC" wrote in message
...
I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed
above
except
in
the Move or Copy Sheet dialog, select (move to end) in the Where
section
of
the dialog.
After stopping the recorder open your VBE and look at the code.
The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in the
workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a
formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the
sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy
Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed
to
=Detail (2)!G7

Does your addin use the same code to create the copies? If
not,
try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet 3).

I also have an addin file that loads with this workbook
and
it
contains
all
the codes to operate the buttons and menus on the
workbook.

In the addin file I have the same three worksheets (recap,
payroll,
and
details). These were put in the addin file...inorder to
run
code
that
copies these worksheets and adds them to the
activeworkbook
incase
the
users
needs more of those sheets. If one sheet is added they
all
three
added
.....as they work together.

So... after a one set of the worksheets have been added..
the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details
(2)

They can added as many sets as needed....my guess is it
won't
go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine......
however..
I
now
have
formulas that are in the Recap sheet that link to the
Details
sheet
and
I
need to add the formula to the added recap sheets...when
it
is
added
and
have it correspond to the added Details sheet that works
with
it...

Is there a way to add a formula to the added Recap sheet
that
will
reference
the added Details sheet .....keep in mind I won't know the
number
of
the
sheets added...as the user can add as many sets as needed.
They
are
added
is sets ... which I think helps with running the code to
do
this..

The formula needs to go into cell D8 of the Recap sheets
added
and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet number
that
has
been
added ( for ex...Recap (2) ) and have code set cell D8 of
the
Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of
sheets
are
added...
the
sheets would be Recap (3), Payroll (3), and Details
(3)...and
the
formula
needed in Recap (3) cell D8 would need to be =Details
(3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly

Hi
Thanks Tom.. that worked..

I now have two more ranges that I'm trying to move formulas over when the
worksheets are added... and they are B1 and C1.
I've tried added the ranges to your code..but it doesn't work for me...see
what I did to the code below..
It doesn't convert the text to a formula.... in B1 and C1.. A1 still works
fine...

With Activeworkbook
for i = .worksheets.count to .worksheets.count - 2 step -1
With Worksheets(i)
.Range("A1").Formula = .range("A1").Value
.Range("B1").Formula = .range("B1").Value
.Range("C1").Formula = .range("C1").Value
End with
Next
End With

Do you know how I would do the same thing for B1 and C1 as you showed me for
A1..??

Thanks for you help...

"Tom Ogilvy" wrote in message
...
With Activeworkbook
for i = .worksheets.count to .worksheets.count - 2 step -1
With Worksheets(i)
.Range("A1").Formula = .range("A1").Value
End with
Next
End With


--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
Thanks!
Since I am adding three sheets at one time (from the addin file to the
activeworkbook) and each of these sheets have the =fein formula in cell
A1....how will I be able to correct the formula in cell A1on each
worksheet
after adding them??
Also.. I will not know the exact name of the worksheets added as a
number
is
added to the name depending on how many times the user has added them.
For example... the active workbook has the following three worksheets in
the
template:
Recap, Payroll, and Detials

When the user adds more worksheets.. (which they all three get added at
one
time with this code):
Workbooks("TestAddin3.xls").Sheets(Array("Recap", "Payroll",
"Details")).Copy After:=ActiveWorkbook.Worksheets(Worksheets.Count)
the active workbook now has the following worksheets:
Recap, Payroll, Details, Recap (2), Payroll (2), Detials (2)

And.. when the users adds another set...the active workbook appears with
these worksheets:
Recap, Payroll, Details, Recap (2), Payroll (2), Detials (2), Recap (3),
Payroll (3), Details (3)
and so on as more are added.

So.. I'm not sure how I would locate the worksheets that were just added
(by
the user).....and then correct the formula in cell A1 of all three
sheets.

Thanks so much for your help!!
I really appreicate it..
Kimberly


"Tom Ogilvy" wrote in message
...
In the addin make the formula (add a single quote at the beginning)
'=fein

then after you copy the sheet correct the formula with

activesheet.Range("A1").Formula = activesheet.Range("A1").Value

--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
I have another question...that just popped up...

When I'm adding these worksheets from the addin file to the Active
Workbook... a cell in worksheet RECAP located in the addin file has
a
formula that references a named range in the active workbook.
For example: Recap B1 (in the addin file) has the following formula
=Fein
( where Fein is a named range in a cell in the active workbook )

The formula appears as #REF! in the addin file.. (as there is no
Fein
Range
in the addin file which I understand).

So when I add the worksheets.... I get the following message:

"A formula or sheet you want to move or copy contains the name
'fein',
which
already exists on the destination worksheet. Do you want to use this
version
of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formual or worksheet, click
No,
and
enter a new name in the Name Confilict dialog box."

When I click yes... everything transfers over fine .... however...my
question is ... Is there a way to bypass this message so the users
do
not
have to be faced with it when adding the worksheets?

Thanks for you help!




"Tom Ogilvy" wrote in message
...
After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

--
Regards,
Tom Ogilvy


"KimberlyC" wrote in message
...
I tried that before I reposted.... and it puts
After: Sheet (3)
I will continue to play around with it...
Thanks again for your help.

"gocush" wrote in message
...
Turn on your macro recorder and do the same steps I followed
above
except
in
the Move or Copy Sheet dialog, select (move to end) in the
Where
section
of
the dialog.
After stopping the recorder open your VBE and look at the
code.
The
portion
of my above code line that reads:
Befo=Sheets(1)

will be different. That's what needs modifying.

"KimberlyC" wrote:

Thank you so much..... that works much better than mine.

One more Question....
How do I get the worksheets to add after the last sheet in
the
workbook.
I
won't know the name of the last sheet.

Thanks for you help!!!

"gocush" wrote in message
...
Kimberly,
I just opened a new wbk, named the three sheets, entered a
formula
=Details!G8 in cell D7 of sheet Recap.
I then used the following code to create copies of the
sheets

Sheets(Array("Recap", "Payroll", "Details")).Copy
Befo=Sheets(1)

This created Recap (2), Payroll (2) and Details (2)
with the correct formula:
In Recap (2) Cell D7 the formula was automatically changed
to
=Detail (2)!G7

Does your addin use the same code to create the copies?
If
not,
try
this.
HTH

"KimberlyC" wrote:

Hi!

I have a workbook that has three worksheets in it.
Recap (sheet 1), Payroll (sheet 2), and Details (sheet
3).

I also have an addin file that loads with this workbook
and
it
contains
all
the codes to operate the buttons and menus on the
workbook.

In the addin file I have the same three worksheets
(recap,
payroll,
and
details). These were put in the addin file...inorder to
run
code
that
copies these worksheets and adds them to the
activeworkbook
incase
the
users
needs more of those sheets. If one sheet is added they
all
three
added
.....as they work together.

So... after a one set of the worksheets have been
added..
the
workbook
contains the following sheets:
Recap, Payroll, Detail, Recap (2), Payroll (2), Details
(2)

They can added as many sets as needed....my guess is it
won't
go
over 20
sets..but you not 100% sure.

Anyway... Adding the sheets is working fine......
however..
I
now
have
formulas that are in the Recap sheet that link to the
Details
sheet
and
I
need to add the formula to the added recap sheets...when
it
is
added
and
have it correspond to the added Details sheet that works
with
it...

Is there a way to add a formula to the added Recap sheet
that
will
reference
the added Details sheet .....keep in mind I won't know
the
number
of
the
sheets added...as the user can add as many sets as
needed.
They
are
added
is sets ... which I think helps with running the code to
do
this..

The formula needs to go into cell D8 of the Recap sheets
added
and
is
=Details(with corresponding page #)!G7

Maybe.... there is a way to look at the Recap sheet
number
that
has
been
added ( for ex...Recap (2) ) and have code set cell D8
of
the
Recap
(2)
to
=Details (2)!G7 and so on... so when a thrid set of
sheets
are
added...
the
sheets would be Recap (3), Payroll (3), and Details
(3)...and
the
formula
needed in Recap (3) cell D8 would need to be =Details
(3)!G7

I hope this made sense....it was hard to explain.. :)

Any help would be greatly appreciated..
Thanks in advance..
Kimberly