VBA Add Formula to varying cell reference

[SpicyItalian]

The premise of my project is that based on user inputs in a tab (Tab1), it will auto calculate the required materials for a PO on a different tab (Tab2). Tab2 has every potential line item that could be ordered. Once the inputs are in place they are multiplied by the cost of each individual unit in a cell to the right.

I have a button that when pressed it deletes all rows that have 0 quantity balances. It then copies the sheet, creates a new workbook, and then pastespecials as values.
The issue I need help with is having a formula generate underneath the sheet after it is pasted as values. The reason i cannot figure this out is because that "sum cell" will be different every time based on the required materials. Is there a way to determine which cell would be the proper one to generate this that would be variable anytime. The cell to the left of my desired "sum cell" has the text "Total". Not sure if this could help to generate some sort of anchor point for this.

Any and all help is appreciated!!

 

[Micron]

I take it that the only direction the summed cell could move after deleting rows is up? I'm not understanding why you need to worry about where it ends up in that case. In other words, if there was only one item (e.g. the 156"x3" one) why wouldn't the summing section just come right after that? Or it does but you don't want it to?

BTW, probably not an issue but you should know that X and FRow variables are Variants because they are not explicitly typed. Maybe you know that and it was intentional. If not, should be
Dim X As Long, FRow As Long, LRow As Long, etc.

 

[Vogateer]

Can you not use a simple offset in that cell, like this:

=SUM($C$4:(OFFSET(C10,-1,0)))

Change $C$4 to be an absolute reference to your first cell in the sum column, and change C10 to be the relative reference for the same cell you're entering the total in. Example:

 

[SpicyItalian]

I take it that the only direction the summed cell could move after deleting rows is up? I'm not understanding why you need to worry about where it ends up in that case. In other words, if there was only one item (e.g. the 156"x3" one) why wouldn't the summing section just come right after that? Or it does but you don't want it to?

BTW, probably not an issue but you should know that X and FRow variables are Variants because they are not explicitly typed. Maybe you know that and it was intentional. If not, should be
Dim X As Long, FRow As Long, LRow As Long, etc.

The summed cell would only move up, that is correct. Currently I have it so that the sheet is does sum it prior to copy and pasting it. I want to make the new sheet have the sum formula after everything is copy pasted so that when I add the other items (Blueprints, installation, freight, etc.) those will auto sum for the user as well. I have the sheet protecting itself after as well so it can't be a manual input post new workbook creation.

 

[SpicyItalian]

Can you not use a simple offset in that cell, like this:

=SUM($C$4:(OFFSET(C10,-1,0)))

Change $C$4 to be an absolute reference to your first cell in the sum column, and change C10 to be the relative reference for the same cell you're entering the total in. Example:
View attachment 93684

That is a pretty good use of that function. I have never thought of that before. This could potentially work. The problem is that I need the sum cell to generate after the new workbook is made and all cells are copy and pasted as values. The cell that this would go into is the "Total Investment" cell so that after the user finalizes the sheet they can enter the ancillary items (freight, installation, etc.) and everything will total up.

 

[Vogateer]

I'm not sure how that last sheet is generated, but a few ideas come to mind, depending on what suits you best:

1. Name the Total cell so that you can place that formula into the cell with VBA. You should be able to replace the C10 above with something like total_cell.Address.
2. Keep that Total cell formula the same, and just hide the rows that you're not using instead of deleting them. I think that might be as simple as replacing Rows(x).Delete with Rows(x).Hidden = True

With number 2, you just need to make sure there's no numbers left behind that are hidden but still adding to the sum.

 

[SpicyItalian]

I'm not sure how that last sheet is generated, but a few ideas come to mind, depending on what suits you best:

1. Name the Total cell so that you can place that formula into the cell with VBA. You should be able to replace the C10 above with something like total_cell.Address.
2. Keep that Total cell formula the same, and just hide the rows that you're not using instead of deleting them. I think that might be as simple as replacing Rows(x).Delete with Rows(x).Hidden = True

With number 2, you just need to make sure there's no numbers left behind that are hidden but still adding to the sum.

That could very much work! I appreciate your help on this!