Copying data based on current month of the year

[D.Lovell]

Dear all,

I am looking to build into a macro, a way for it to know what the current month of the year is. The idea is that it will find and copy sales data from a source workbook, to a target workbook containing a pre-arranged template, in order to create a sales analysis. I need to build into the macro a way for it to only copy this year's sales figures upto the end of the previous month, and always do this correctly at whatever time of the year it is run.

I have put the code in below that I have so far which will find the lines for "AREA 1" in column A, "SALES" in column D, and the year in column F of the source data workbook and copy the required data across to the target workbook into the correct columns of the 2 sheets, for 2013 & 2014.

I have attached the example I am using for the source data workbook and the target workbook, the below code exists as a macro within the target workbook entitled "Sales Analysis Template1.xlsm".

What I want to do, is when coping the figures to the 2014 sheet, to exclude the months of the year that haven't occurred yet, including the current month. This needs to always work correctly at whatever time of the year the macro is run. So if we run it now, we want to end up with only Jan, Feb & March completed on the 2014 sheet, the rest of the monthly columns should be left blank.

If anyone can help me out with this it would be much appreciated.

Regards,

Dave.

Please Login or Register  to view this content.

[slx]

You can use Month() and Year(). Date will give you today's date. You can do stuff like Month(Date) and that will give you current month in number form.

[D.Lovell]

Yes I am aware of those functions for writing formulas in a cell. I have no idea how to write what I have described into the above code, I am looking for someone to show me an example of how to do that is it is possible.

When finding the data in the source workbook that matches "AREA 1" in column A, "SALES" in column D, and "2014" in column F, I want to then identify how many of the columns from G-R are relevant now based on what month of the year it is, and then copy only those columns of data starting from G on the source workbook, and the pasted starting from column C on the target workbook. The columns that represent the months of the year that haven't occurred yet need to be left blank on the 2014 sheet, including the current month.