Update Table Header Date with VBA

I've got a spreadsheet that my company has me update every quarter. I'm trying to automate the entire update process, but am quite new to VBA so need some help getting started.
At the end of the day what I want to accomplish is to have VBA read a date from a specified “input” cell that I will manually fill in and update the dates in corresponding cells in other worksheets. However the cells in the other tabs are not purely date fields, they are a mix of string and date.

In my first tab, labeled “input” I put in to cell A2 the quarter ending date (6/30/14). What I want VBA to do is read this input cell put that in to the correct table headings on the next tab. In the next tab I have three tables. The header for the second column heading of each of these tables reads "IRR as of <date>". I want to be able to click a button and have the <date> updated to the quarter ending date that I input on the first tab. The text part at the beginning of the header, "IRR as of", needs to remain the same. Only the date part should change.

I don't think that my cell references will change, ie the tables will usually have the same number of rows and columns, but just in case these tables do end up moving around I'd like VBA to be able to look up the defined range (more specifically the table's name) and find the appropriate column.

Thanks in advance

Just type a valid quarter ending date!!!

Please Login or Register  to view this content.

Thanks a million guys. All three of these solutions work great.

Can I just ask a quick follow up question? Is there any way to have VBA look for a specified named table range when spitting out the output? Currently the macros are putting the updated data in a specified cell which may or may not be the target cell in the next version. My concerns with the current solution are twofold.

1) what if my tables move? Currently my column headers are found in G2, 10 and 18, but if a few more rows are added to one of the tables, then the output will be put in to a cell that doesn't correspond with the table header.

2) In the real spreadsheet I have seven tabs each with six tables. It wouldn't be the end of the world to look up the cell references for each table column header, but it might be a smidge quicker if i can just tell VBA to spit out the output at "Sheet4.table18.columnheader2".

Originally Posted by msexcelathome

The Solution which I have given is based on the Word "Partnership name". Wherever you put that word, the Date will come in the next cell..

Deep

You are absolutely right. I must have missed that. Thanks again!