VBA Insert Formula to SumIf with Variable Sheet Names

This might be my biggest challenge yet (granted I'm new to VBA). In the attached file I have a Capital sheet. This is where my managers can see their current asset costs and put any upcoming purchases. I need this data to feed into my cost center sheets. I need to be able to insert a formula in the cost center sheet that will be updated with any changes made in the Capital sheet. The challenge is...there will be multiple Capital sheets in the file as there are multiple cost center sheets in the file. I've played around with a SumIf formula in Excel that I can get to pull the data using a variable

Please Login or Register  to view this content.

but I need to be able to have the sheet name as a variable too. I have been trying to write a macro that can say If the first five of the cost center sheet name = the last right of the Capital name then the SumIf formula would be populated with the matching Capital sheet name. I would really appreciate any help that you could give.

Thank y

When I try to use VBA to populate a cell with that code I get an Application-defined or object-defined error. Can you please help me figure out what is wrong (I tried using .value and .FormulaR1C1)?

Thank you,

Please Login or Register  to view this content.

AlphaFrog,

When the cell is populated by the formula I get a REF error.

AlphaFrog,

Can you please help me understand parts of this code? When it populates the cell I see the following:

Please Login or Register  to view this content.

I know what the C2 and C9 are referring to. I don't understand the RC1 although I think it has to do with taking the left six digits of Column A (account number). Although this macro populates the cells with the code above (changing in each cell), it does not appear to be working quite right because I am getting zeros instead of the numbers from the Capital sheet.

Thank you once again for your time and efforts to help!

AlphaFrog,

I am using the same formula you created above to populate my cost center sheet with data from my Maintenance sheet. The Maintenance sheet looks very similar to the Capital sheet except the data that I need starts in column K (Aug) instead of column I like it did in the Capital sheet. So, the cost center sheet column I needs to bring in the data from the Maintenance sheet column K and so on through the end of 2022. I have updated my file to better explain what I am talking about. This is the code I am currently using but it is bringing column I to I.

Please Login or Register  to view this content.

AlphaFrog,

Thank you so much for all of your help! All of this works great!!! I ran across one issue during final testing today. Not all cost center tabs seem to have a Capital or Maintenance tab to populate from. How would I tell the macro if a sheet doesn't exist to skip inserting the formulas? I've tried various things but it tends to skip doing anything.

AlphaFrog,

This is great and the last piece I needed to finish up this part of my project! The research I did online did not even mention Evaluate. I was trying to use SheetsExist and that was not obviously working. Thank you again for everything!