Copy Data from relavent page

[tharindudk]

Hi,

I have a mainpage with a button which creates a copy of the template every time I click it.

Eg When Button 1 is clocked once - It creates two new worksheets called TIPT_Site_1 and TIPT_CALC_1
if clicked again it creates two more new worksheets called TIPT_Site_2 and TIPT_CALC_3, so everytime it clicks, creates new sheets and name them as 1, 2, 3, 4, 5... so on.


What i want to do is use the values entered in TIPT_Site_1 in TIPT_CALC_1, for TIPT_CALC_2 use values from TIPT_Site_2... and so on.

So prettymuch I want the two newly created worksheets to be linked so it uses the right values.

If I wanted to use Value of TIPT_SIte_1 A5 in TIPT_CALC_1 in cell B6 i know i can use =(TIPT_Site_1!A5). but then for the other once i need to change it manualy again to =(TIPT_Site_2!A5).... And this is not practicle. I want the equaltion of these cells to change automaticaly.

Is there a piece of VBA code I can use to do this.
Thankyou

Ay help is much appriciated

[millz]

Hi, it would be much better if you provide the code that you are currently using. So people can amend from there, rather than coming up with a new piece of code.

[tharindudk]

Hi,

This is the Workbook I created.

Thanks

[millz]

Which are the cells that needs the formula updated? Doesn't seem to have anything in cells A5 or B6 in any sheets like you mentioned.

[ugogirl2005]

You should be able to create formulas using the method below. I usually try to define variables for my sheets, but using the variables you already have I've inserted your example in the proper place in the code below. "...in cell B6 i know i can use =(TIPT_Site_1!A5)."

Please Login or Register  to view this content.

[millz]

Just found the cells in the CALC sheet. Try adding this just below the code that copies the CALC sheet.

Please Login or Register  to view this content.

[tharindudk]

Thankyou Verymuch. It works.

[tharindudk]

HI, One more question.

How can I use value from ("TIPT_Site_BW_CALC " & x ) in "=(TIPT_Site_" & x )
What I want to do is Copy the total band with calculated in to Tipt site page.

Thankyou

[millz]

If I understood you correctly.

Try adding this below the code in post #6

Please Login or Register  to view this content.

[tharindudk]

Thanks... Everything works as I want. Much appritiated your help