Copy a row of formulas but change sheet name in formulas based on input from user

[Gabbelgak]

Hello, this is my first question for the community. (I know I should probably have switched to access but I'd like to do this in excel if I can)

I have many sheet excel file I use to store customer transaction logs on. One page per customer.

On another page I have a sheet that checks to see how many of each item I've received and given to all customers for the current day.

This is repeated down every cell in a column for each "Item" I have.
=COUNTIFS('CustomerA'!$A:$A,TODAY(),'CustomerA'!$B:$B,"Item1",'CustomerA'!$C:$C,">""")
=COUNTIFS('CustomerE'!$A:$A,TODAY(),'CustomerA'!$B:$B,"Item1",'CustomerA'!$E:$E,">""")
=COUNTIFS('CustomerA'!$A:$A,TODAY(),'CustomerA'!$B:$B,"Item2",'CustomerA'!$C:$C,">""")
=COUNTIFS('CustomerE'!$A:$A,TODAY(),'CustomerA'!$B:$B,"Item2",'CustomerA'!$E:$E,">""")

This has worked great except now I'm wondering what I'm going to do when our company gets a new customer and I'm no longer there to manage the file.

I would like to create an option for the user to press a button and it will copy the above formulas to the next empty column but replace the sheet name (Either an input box or cell value they alter it makes no difference)

 

[SQUIDD]

Hi

So when you are no longer in the company.
Will the user have to create a new sheet for the new customer 1st?
I think if it was me, i would perhaps create a small userform to add a new customer that creates the new sheet and sets up your formula upon button click?
Then everthing gets done without many places for the new user to make mistakes.

Would that be something you would consider?

Dave

 

[Gabbelgak]

Yes the user would need to create a sheet.

I was going to have a button to just copy in a saved template sheet. The actual sheet for each customer contains no formulas though. The only sheet that has the formulas is a daily counts sheet so I need to update the daily counts sheet based off the name of the new template sheet they just added.

 

[SQUIDD]

ok

that should be fairly simple to do.
so the new set of formula for the "daily counts" will go into the next available empty column?

Dave

 

[SQUIDD]

Sorry

Forgot to mention, we can use vba to write thee formula into the next cell for you and copy down.
So would need to know where the formula start position would be and where to cpy down to.

Dave

 

[Gabbelgak]

That is correct. It will go to the next empty column.

As of right now the start position for each formula is AB2 to AB43 would currently be the next set of positions to copy to in the formula.

I would like the formula to drag down til there is no item associated with it in the A column though if possible as I will also be adding a button for creating new inventory that is going to expand this sheet down further over time.

 

[Gabbelgak]

I didn't even think to try to make a macro of find a replace. Doing so gave me the code below, I can tweek it to get to the results I desire but if anyone has better solutions available I'd love to take a look.

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWindow.SmallScroll Down:=18
Range("S2:S43").Select
ActiveWindow.SmallScroll Down:=-27
Selection.Replace What:="Template", Replacement:="New", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub