Copying data from 1 wbook with changing name to Master wbook & consolidating into 1 row

[bluepinky]

Dear all

Welcome to my first post (other than introductions )

I have made a start with writing the macro by use of other peoples coding so I won't take credit for what I have. However, I'm completely stuck.
Aim: To Copy data from workbook termed "[Date]Daily Report" ([Date] signifies that this part of the file name changes daily) to paste into a Master workbook termed "Test Macro Build" into alternate columns by [date] ensuring that the analyst data is pasted into the correct column. I attach both workbooks. 01-10-2012_Daily report.xlsx Test macro build.xlsm

The macro as it stands is in "Test Macro Build"

I have tried to add in an input box so the user can specify the date where the pasting should start. For example if the daily report is the for the 01-10-2012 then pasting of the data should be in the row corresponding to this date.

I would like to retain the ability of just moving files to a location where the data is automatically copy/pasted depending on the start date (user defined) in case there are more files in the location than there should be, however I think that adds further complexity and would be willing to discuss if need be.

I hope its clear what I am trying to do I have spent many hours just to get this far and have browsed the forum for inspiration and related posts but just can't put the pieces of code together. Your help would be greatly appreciated.

Many thanks

[bluepinky]

*Bump* No response

[Chippy]

Try this code in a copy of your Test macro build.xlsm workbook. Note that in the "Compl by analyst" sheet the column for Analyst 1 Project 5 points is missing, which causes the data to be imported into the wrong columns after the missing column. Therefore you should insert the missing column before running this code.

Please Login or Register  to view this content.

[bluepinky]

Hi Chippy

Thanks for this code, there is no way in a million years I would have got to this lol so the help is very appreciated. However, there is just one snag. The first analyst data all goes into the points column rather than the project columns. I have tried playing with some offset numbers but I can't seem to get the first analyst data where it should be. Can you tweek the code please?

many thanks

[bluepinky]

Ok so I didn't read your intro carefully enough. I'll try when I insert the extra column. Doh!
Thanks.

[Chippy]

The first analyst data all goes into the points column rather than the project columns.

Well, I assumed the points should go in the columns headed 'points'. To put the points in the Project 1, Project 2, etc. columns instead, change the 3 + r * 10 + c * 2 calculation to 2 + r * 10 + c * 2, like this (I've reflected this change in the comment too):

Please Login or Register  to view this content.

Yes, you need to insert the missing column as I said - column K, the 'points' column heading for Analyst 1 Project 5.