Re: Lookup & open file based on partial filename
So some important questions first:
1. You say you want to use the "Updated as of" instead of AM1 - fair enough I am all for removing hard coding where possible. However, I assume the date will always be 1 column to the right of this string. Remember searching for this string is easy but adds to processing overhead slightly.
2. If AL-AO are not fixed then the easiest thing to do would be to find the first "2011" (in this case) in row 2 and assume this is the start of 4 columns of data. I assume this means you could be adding data which pushes the 2011 running total column further over.
3. Based on my assumption in point 2, I assume there will only be one column that has the tag "Running Total" in it...ie, there won't be a running total for 2011 followed a running total for 2012, etc?
In response to your reverting back to Pre-Tax cash flow question - this code technically does not exist as you asked for it to be changed to the calculation based on running total. If you need both, you have a number of options from using a named variable to adding a second "starting" macro which decides whether or not to paste a straight value into AL or a calculated value into AL ie, you would have two macro buttons on your master sheet. If this option does not change often, I would go with a named variable which has a TRUE or FALSE value, something like "PRE-TAX-APPLY-CALC = TRUE" (or FALSE).
At this rate, I think this thread needs to move to the "Hire Help" section, I hope you're not telling your masters this is your work!:)