Setting a formula with code in worksheet added

[KimberlyC]

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

 

[Guest]

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 Before:=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]

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!!!

 

[Guest]

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:
Before:=Sheets(1)

will be different. That's what needs modifying.

 

[KimberlyC]

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.

 

[Tom Ogilvy]

After: Sheet (3)

should be

After: Worksheets(Worksheets.count))

 

[KimberlyC]

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]

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

 

[KimberlyC]

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]

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

 

[KimberlyC]

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...