Add formula to sheet sum values from another

[andrewdp3]

I've been trying different pieces of code to figure this one out but to no avail. I have a listbox on a form that populates when users select an option button. When they select something from the listbox and click ok the code makes a copy of a template sheet and places in workbook. It also renames the sheet with the name of the selected item in the listbox. Last it takes the same name selected in the listbox and adds it to the next empty spot on a sheet called "Main" (Column B).

All of this works fine except I need one final piece to the code for everything to work. I'm that once the code executes it will also insert a formula into the "Main" sheet (Column D) of the current row.

The formula just takes the sum from range D4:D75 of the copied template sheet just created. This way each time it copies and renames the sheet it adds the sum formula to the "Main" sheet. This is what I have tried but I don't know what I'm doing - Any ideas?

Private Sub cmdAdd_Click()
Dim curRow As Integer

curRow = 5
Sheets("M Template").Visible = True
Sheets("M Template").Select
Sheets("M Template").Copy Before:=Sheets("Hour Sheet")
Sheets("M Template (2)").Select
Sheets("M Template (2)").Cells(2, 2).Value = Me.lbxAccount.Value
Sheets("M Template (2)").Name = Me.lbxAccount.Value

Do Until Sheets("Main").Cells(curRow, 2).Value = Empty
curRow = curRow + 1
Loop

Sheets("Main").Cells(curRow, 2).Value = Me.lbxAccount.Value
Sheets("Main").Cells(curRow, 4).Formula = Sum(Sheets(Me.lbxAccount.Value).Range("F4:F75"))

Sheets("M Template").Visible = False

 

[VoG]

Perhaps

Sheets("Main").Cells(curRow, 4).Formula = "=Sum(Me.lbxAccount.Value!F4:F75))"

 

[andrewdp3]

It gives me a run-time error 1004
Application defined or object defined error

 

[VoG]

Silly me. Try

Sheets("Main").Cells(curRow, 4).Formula = "=Sum(" & Me.lbxAccount.Value & "!F4:F75))"

 

[andrewdp3]

I've tried the code a second time but for some reason same error occurs -

 

[VoG]

Sorry, extraneous closing parenthesis:

Sheets("Main").Cells(currow, 4).Formula = "=Sum(" & Me.lbxAccount.Value & "!F4:F75)"

 

[andrewdp3]

That code worked perfectly thank you very much! I was wondering if we could change it just a bit.

The idea would be an "if" statement but I'm unsure exactly how?

If the sum of D4:D75 is greater than the sum of E4:E75 of the same sheet. I would like to subtract the sum of E4:E75 from the sum of D4:D75 and place that answer on the current row of the other sheet in the D column.

Else subtract the sum of E4:E75 from the sum of D4:D75 and place that answer on the current row of the other sheet in the E column? Is this possible??

Thanks again

 

[VoG]

That seems to me to be a different question so please start a new thread.