Macro to copy data from source workbook to target, based on current month of the year

Dear all,

I am looking for someone to write an example piece of code to demonstrate how to achieve taking into account the current month of the year when copying data from one workbook to another. This is for something I am writing a macro for and am learning as I go.

For the purpose of this, I attached an example of the source data workbook, and the target workbook into which certain lines of data need to be copied. The source workbook is entitled "SOURCEDATAEXAMPLE", and the target workbook is entitled "Sales Analysis Template1".

What I want this example Macro to do is find all the lines in the source workbook that match "AREA 1" in column A, "SALES" in column D, and "2014" in column F. For each of these lines I want to copy the data from columns B&C and paste in columns A&B in the target workbook, on the 2014 sheet. Also for these lines I want to copy the data from columns G-R in the source workbook, and paste to columns C-N in the target workbook, on the 2014 sheet.

Now here is the part regarding the current month...

The data in columns G-R in the source workbook represent sales figures for the months of the year, i.e. Jan-Dec. I want the macro to only copy the figures from the source workbook to the target up until the current month of the year (not including the current month), and for this to always work correctly weather it is run now, or later in the year etc. So we need to copy the figures starting from column G and going up to which ever column depending on what month of the year it is now, and then paste the data into the respective columns in the target workbook, starting from column C and going up to whatever it is.

So running it right now in April, it should copy columns G-I from the source workbook and paste into columns C-E on the target workbook. If run next month it would need to be columns G-J from the source copied to columns C-F on the target, and so on.

Hopefully I have explained enough for you to understand exactly what I am looking for.

If anyone could have a go at this for me it would be much appreciated.

Many thanks in advance.

Maybe I should try and explain further, here is example code that will find lines as I described above and copy to the template workbook (it actually does a bit more than I described above as is finding lines to copy to both sheets on my example)...

Please Login or Register  to view this content.

I want to modify it so that when filling in the figures on the 2014 sheet it doesn't copy the sales figures for months of the year that haven't passed yet (including the current month). So i'm thinking to modify this part...

Please Login or Register  to view this content.

One idea I had is maybe using .offset and Month(Date)-1 it might be possible to copy only the columns of data that correspond to passed months of the year, but I am inexperienced with VBA and not getting anywhere with it so far unfortunately.

Can anyone please show an example of how to modify the above code as I have described with regards to the part where it is copying columns G-R from the source workbook into columns C-N on the 2014 sheet of the Sales Analysis Template workbook?

Hi John,

I look forward to seeing it, much appreciated.

Very helpful thank you, completely different way to do it to the other example. I have made a few adjustments to it to only paste values, I have adjusted one of the column references because it is actually the data from column C in the source workbook that I want pasted onto column B of the target workbook, and I have added the part that deletes the left-over rows afterwards.......

Please Login or Register  to view this content.

The previous example also finds the lines that match all the same criteria except for column F being "2013", and copies that data to the 2013 sheet of the target workbook, in the same layout as for the 2014 sheet. Could you please add that part into your example code? It doesn't matter about month of the year for the 2013 sheet, every line that matches the criteria will need the whole of columns G-R copied from the source workbook into columns C-N of the target workbook, on the 2013 sheet.

Thanks, no hurry, your help is much appreciated

I messed about with it a little and got what I need, great thanks for this.

I have it like this....

Please Login or Register  to view this content.

The point of deleting the leftover rows is so that the summary section was moved up accordingly on the 2014 sheet, only applies to that sheet.

I will be keeping the template sheet with macro unchanged for repeated use, each time I create a report, I will save separately so the template always remains as it is.

I reattach my example which now does what I needed it to do.

Thanks again for your help