|In the screen shot that you sent me, you appear to have monthly dates starting in K371 and continuing along that row. For this solution to work, those must be actual dates, e.g. 10/1/2012, formatted as Oct-12, Nov-12, etc. or however you wish. The key point is that they must be dates, not text.|
1 - Right-Click the sheet tab and choose View Code to open the VBA Editor. You should see large empty pane on the right, and a pane named "Project - VBA Project" on the left. If you don't see the left hand pane, click View...Project Explorer to open it.
2- In the Project Explorer, you should a list of the Sheet names for the sheets in your workbook, along with a listing for This Workbook.
3 - Double click the words This Workbook to open a new pane on the right. The word "General" in the drop down at the top of the right hand pane should change to Workbook.
4 - Paste this code into the Workbook pane:
Private Sub Workbook_Open()
'Determine last column with value in Row 373
lastDateCol = Cells(373, Columns.Count).End(xlToLeft).Column
'Check if the last day of current month is greater than
'the last day of the month for last date in Row 371
If Application.WorksheetFunction.EoMonth(Date, 0) > _
Application.WorksheetFunction.EoMonth(Cells(371, lastDateCol), 0) Then
'If it is, then it must be a new month.
'Put value from C355 in the next open cell in Row 373
Cells(373, lastDateCol + 1) = Cells(355, 3)
5 - Save the workbook as a macro enabled workbook (.xlsm)
Here's how's this should work:
Each time the workbook is opened, this code will run automatically. The code will first determines the last cell in Row 373 that contains data. e.g. in your screenshot, the last cell with data in Row 373 is L373.
The code will then compare the date in L371 with the system date by using the EoMonth (End Of Month) function which returns the last day of the month referenced by the function. If the last day of the month of the system date is greater than the last day of the month in Row 371 of the same column as the last piece of data in Row 373, then it must be the first of a new month or later. If it is, the code will put the value currently in C355 in the next cell in Row 373. For the rest of the month, the EoMonth function will return the same value for the system date as for the date in the last Column of Row 373, so no action will be taken. On the first day of the next month that the file is opened, the EoMonth value for the system date will be greater than the date above the last piece of data in Row 373 and the value from C355 will be copied into the next cell.
The only way for you to test this code prior to the first of next month is to close the file, change the system date of the computer to the next month and then open the file. Once you are satisfied that it works, you can erase the new value or just not save the file. Of course, you also have to remember to reset the system date to the correct date.
As written, the code will not work correctly if the file is not opened at least once a month. i.e. if the file is opened on Oct 29, and then not opened again until Dec 1, it is going to put the value from C355 in the column for Nov-12 since that cell will not be filled in yet. This could be fixed with a few more lines of code, but since you said that the file is opened almost every day, I didn't bother dealing with the issue of a skipped month.
You should test this code in a backup copy of your workbook in case things go terribly wrong.
Let me know how this works out for you.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.