Macro for linking cells from different rows to a specific place on other worksheet

Hey RealNiceGuy, I am seeking a VERY similar solution. I decided it might be better to post here than flood the forum with a new topic. Attached is my excel file let me give you a little background: (This sounds way more complicated than it is I promise)

This a spreadsheet for making estimates.

1.) When you enter the spread sheet click on the requirements tab, you will see a gray buttton on top of yellow cells. Click that button.
2.)3 things just happenend
a listbox popped up, populated from a list on a hidden sheet.
4 columns, titled by the selection, just appeared on that requirements sheet.
A spreadsheet titled by the selectino just appeared.
3.) As you can see those 4 columns are seperate groups, each colum is summed to the bottom in the totals row.
What i am trying to do is take the 4 new cells in the total row and link it to the cells "A2:D2" on that applications corrosponding spreadsheet. each time a new app is added, 4 new columns will appear, 4 new total values will need to be linked to that new app's spreadsheets "A2:D2" cells.

As new line items are added and new values placed in those columns the total will change, as those totals change I need the cells linked so the totals on the other spreadsheets change as well. I tried doing this in a very similar fashion to the above file, but I wasnt quite able to get it to work. (my code is on requirements sheet)

Thank you so much in advance.

Hey thank you for simplifying my code, I am a little confused, as i still have a couple of issues.

1.) when values are added to those generated columns despite the totals row carying the formulas the totals arent updated. You have to click in the totals box, click on the formula and hit enter for it to update.
2.) Even after forcing the totals row to update I dont see where it copys that data over to A2-D2 on that applications corrosponding spreadsheet.

Did opening this in 2007 change things, or were you not able to look at that?

Thank you so so much, you are the best.

Well...

Are you planning on hitting the button on worksheet(Requirements) more than once? The First time you hit the button columns(H:K) are copied over then if you hit again columns(L:O) are copied over. This would be an endless cycle till you ran out of columns.

Anyway lets take the first time you hit your button your looking for a way to copy H84:K84 to where? and if you hit the button a second time you would want to copy L84:O84 To somewhere? Correct?

I also noticed you have a worksheet change event in place on your REQ worksheet, This changes the values in the new page that was just created? However would you not just want to add the values or links at the time the columns are created on your REQ worksheet? from the script in mod 1 ?

I also noticed a lot of other information are you planing on moving all that as well? For example on copy that is created we are only filling in D37 with the value in H84 * Sheet Table H3?

My copy of the generic check list is filled with #ref errors so I dont know what cell they are trying to link to.

I'm sure there are many more questions as well, anyway can you try an walk me through again what is suppose to happen when you click the button. let me know what is working and what is not, after we get that part down you can let me know any additions you need to take place.

Thanks ,Mike

Ok so I see what you are trying to do, but the problem is those total values will change, as those values change they need to change on that apps corrosponding spreadsheet, that why I thought it should be in that spreadsheets code. Here this does exactly what I want, but I would have to repeat this 30 times (for up to 30 applications), is there anyway to automate this process (below?). Also im having the same issue with the application spreadsheets cells as I am my requirements total cells. Even though the formula is there, I have to click on the formula to highlite the cells and click enter to get the formula to work again. If I can automate this below process, and make it to where the formulas are activated, then I should be good.

  Set rightmarker = Sheets("Requirements").Range("B22").End(xlToRight)
   width = Sheets("Requirements").Range("B22", rightmarker).count

If width = 6 Then
    Sheets(app).Range("A2").Formula = "=Requirements!H84"
    Sheets(app).Range("B2").Formula = "=Requirements!I84"
     Sheets(app).Range("C2").Formula = "=Requirements!J84"
     Sheets(app).Range("D2").Formula = "=Requirements!K84"
 End If
 
 If width = 10 Then
    Sheets(app).Range("A2").Formula = "=Requirements!L84"
    Sheets(app).Range("B2").Formula = "=Requirements!M84"
    Sheets(app).Range("C2").Formula = "=Requirements!N84"
    Sheets(app).Range("D2").Formula = "=Requirements!O84"
 End If
 
 If width = 14 Then
    Sheets(app).Range("A2").Formula = "=Requirements!P84"
    Sheets(app).Range("B2").Formula = "=Requirements!Q84"
    Sheets(app).Range("C2").Formula = "=Requirements!R84"
    Sheets(app).Range("D2").Formula = "=Requirements!S84"
 End If

Ok let me re explain as I think it is confusing.

Initially you have 7 worksheets
Instructions, Project Summary, Requirements, Tables, Ammendment History, AIT, Generic Checklist. Everything but Instructions, Project Summary, and Requirements are hidden.

When an estimate is initiated the first thing is to copy down all of that projects requirements on the requirements page and then estimates are made on a per requirement basis. So after all of the requirements are added to the table they start with the first requirement and say "oh that affects application x", so they click the button and select that application where it adds 4 columns of fields next to the requirements. It also copies the "Generic Checklist" spread sheet into a spreadsheet titled with that applications name. This process is repeated as the move down the requirements list, in the end their may be up to 10 applications effected by the project and there for estimates need to be made for each.

What am i trying to do:

Every time an application is added I need to link the 4 total cells (one per addd column) to the 4 total cells formatted the same way in A2-D2 of that projects application page (the one we just created). After an estimate is made per requirement that check list is what they will go down and verify that they accounted for everything within each application. My whole issue is with linking cells between pages, as the pages are created, but not just the values of the actually cells. As the requirements are estimated more and more hours may be added to those columns, thus affecting the totals, which likewise needs to update the totals on each of those pages.

On a side note, I have noticed that despite having the formulas in those columns (sum the column into the total row) it isnt doing it. I have to click on the formula then hit the enter key. Is there any function that updates pages and reruns the formulas? This is minor, but important if I am giong to be referencing the totals row, I need that data to be accurate.

I reattached the file, that D37 stuff was just me trying to make things work. I had code on the requirements worksheet page as I was also trying to do it that way (so that anytime something on the sheet changed it would re link those cells) , but I couldnt make that work. I cleaned up my code alot, and deleted all of my "practice code". Eventually I will have data flying all of the place from sheet to sheet, but If you could just help me with copying the total cels into that applications respective spread sheet then I can copy how you did that for the rest.

THANK U SO MUCH