How to turn sum(first:last!cell) into VBA?

[vmpage]

I have searched and searched and can't seem to find anything that answers my question.

I have a master workbook that compiles files, then adds a "first" and "last" sheet. This part works great!

I have multiple cells that I want to sum across all the compiled sheets. I can do this manually with =sum(first:last!d11) (D11 i s the first cell that I need to sum).

I would like to set up a VBA that would sum the cells from the compiled sheets.

How would I accomplish this?

Thank you.

 

[alansidman]

Instead of a VBA solution, you might want to try using the Consolidate function built into Excel. Look at this link for an understanding

http://www.wikihow.com/Consolidate-in-Excel

 

[FDibbins]

Im not really sure what the problem is here?
If you need o sum the same range across your sheets, why not just...
=sum(first:last!d11:J11)

 

[vmpage]

Im not really sure what the problem is here?
If you need o sum the same range across your sheets, why not just...
=sum(first:last!d11:J11)

The problem is because it's a dynamic book. When I compile the files the formula erases the first:last portion and leaves me with #Ref ! errors.
I was hoping that if there is a way to put that sum formula into a code, then I can assign a button and simply recalculate after each compile.

Thank you very much for the response. I hope my explanation makes sense!

 

[vmpage]

I just thought of something...
I think my problem is that my First and Last sheets are being inserted when I compile rather than being constant pages.

Sub Compile()
Path = "C:\Users\vanessa.page\Desktop\Safety\123456789"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop

Worksheets.Add(After:=Worksheets("Master")).Name = "First"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Last"
End Sub


if I have First and Last as constant sheets, would the formula work all the time? If so how would I modify the above so that the compile placed the sheets between first and last?

Thank you again for the help!

 

[vmpage]

Yep, adding First and Last as static sheets and adjusting the compile macro fixed it!

Thank you again for making me think!!

 

[FDibbins]

The problem is because it's a dynamic book. When I compile the files the formula erases the first:last portion and leaves me with #Ref ! errors.
I was hoping that if there is a way to put that sum formula into a code, then I can assign a button and simply recalculate after each compile.

Thank you very much for the response. I hope my explanation makes sense!

So are you saying that compile removes sheets, or did I misunderstand you?

I have frequently added a sheet called Start (or First) and another sheet called Last, and pulled any sheets I need to be included, in between them. If this is not what you had/meant, perhaps consider doing that?

 

[vmpage]

So are you saying that compile removes sheets, or did I misunderstand you?

I have frequently added a sheet called Start (or First) and another sheet called Last, and pulled any sheets I need to be included, in between them. If this is not what you had/meant, perhaps consider doing that?

When I read your answer to myself the light bulb in my head went off. Once i redid the macro to insert the sheets between first and last the formula was fine.

Thank you again for the help. I do really appreciate it!

 

[FDibbins]

Happy to help