Copy paste data from a Master file to Multiple Reports

Hi All,

I am pushed against a challenge wherein I need some help!

I’ve searched the net thoroughly, but haven’t found a solution that caters to my requirement. There are several Macros to Copy data from multiple excel files to single master file. I use some of them too. However, need some sort of Macro to accomplish a task wherein I spend close to 8-hours daily.

What I am looking for is your kind help in being able to do with a Macro:

• Populate row data from specific columns from a “Master File” to a bunch of customer specific reports placed in a folder on a daily basis.
• Master File has the data for all the clients in our universe
• There are 300-500 client specific reports in a folder. I need to be able to pull the data for each of them from the “Master File”
• Column A will have the customer file name against each row
• Column B will have the tab name

I am currently working on the sample files to facilitate better explanation of my request, which I can provide early tomorrow, but thought it best to seek some guidance if this is doable. I haven’t found anything on the net as such. However, there are ample solutions to consolidate data into one sheet.

Request your expertise. Please advise your thoughts…

Regards,
Spi

This sounds very doable, though I have no idea where 'reverse engineering' would come into it.

Thanks a bunch for your reply! Ooooopppsss.....I merely used 'reverse engineering' because I did not find any solution related to splitting of data. My bad!

Would you suggest me to change the subject line?

I am glad and will post my files for assistance - Master, Sample Before and Sample After with dummy data by early tomorrow.

Thanks,
Spi

Spi

Just remove the Reverse Engineering bit, the rest of the title kind of explains what you want to do.

By the way, this is actually quite a common thing though usually the data would be plugged into some sort of template rather than specific files for each customer.

Thanks Norie!

As advised, the subject line has been amended.

I really want them to go to the existing specific files for each. This is because these are cumulative reports that are updated with daily data. A template won't do the trick as it will still require me to manually update each report, which is my current and very time consuming process.

Regards,
Spi

Eh, I wasn't suggesting a template.

I was just commenting that usually when doing this sort of thing the data goes to a template.

Oh...got it! My bad again. Sorry!

Thanks a bunch for your inputs! I'm getting down to prepare the sample reports will post them early tomorrow. Now understanding it can be done will have a peaceful sleep tonight

Column A with the customer file name against each row, and Column B with the tab name that needs to be populated should suffice the need to select each report. I guess!

You all are true champions! I'm really lucky to be a part of this awesome community.

Look forward to help!

Best regards,
Spi

Good Day!

May I request for some assistance in minimizing the time and effort spent on a repetitive task that I need to do on a daily basis. Currently, I do this manually and it takes me good 8-hours…
My request is to get assistance with a VBA script that can do the following:

Populate row data from specific columns from a “Master File” to the reports

• Master File has the data for all the clients in our universe
• There are 300-500 client specific reports in a folder. I need to be able to pull the data for each of them from the “Master File”
• Client file names are listed in Column A of the Master File
• There can be several rows of data in the Master File that need to go into a client specific report depending upon the report name stated in Column A of the Master File
• The macro will need to insert the data from the rows in the Master File between the last “Data Row” and the “Grand Total” row
• The data needs to be updated in the tab which is named as ABC_13, BCD_13, XYZ TAC_13 etc. Tab name is in Column B of the Master File against each row

To facilitate and explain it further – I’ve attached the below listed:

• Master File
• A sample report “ABC Report20130712" – Before version
• A sample report “ABC Report20130712” – After version (with expected result)

I have 300-500 reports with Column Data A to U remaining constant daily. There can be variant number of columns after column U, but that part is what I will do manually as I have been doing it.

Hope I have explained my request clearly. I’ll be highly thankful if you could help me with this. It will go a long way to help me concentrate on more critical tasks.

Deeply appreciate your help and expertise.

Many thanks,
Spi

I was looking forward to some help on this please....Please help accomplish!

Bump! Please help. Thanks

Sorry for not getting back to you sooner.

I've looked at the file and though it does have the Report name and the name of the tab to be updated, there's no information on where things are actually located.

No problem at all. Appreciate your response.

If by things you are referring to the path it can be locally on any desktop.

The process is like:

A single folder with all customer specific files
A Master file, which has all the data that needs to be populated to each customer report.

Please let me know if this doesn't answer or make sense.

Thank you!

Will the reports be in the same directory as the master file?

Yes, that is correct!

Hi Norie,

Just wanted to confirm if you were able to spare some time on my request. Please let me know if you have any questions.

Thanks,
Spi

Hi Norie,

Afraid to ask. But were you able to look through my request.

Thanks,
Spi

Hi Spi

Couple of questions:

• How many Rows of data are there in the actual Master File?
• Can the Master File be modified to include a Marker Column to indicated a Master File Row has already been processed?
• Will there ALWAYS be an existing Client File for ALL items in the Master File?

Hi John

Trust you are doing great!

Thank you so much for your response.

Here are the answer:

1). There is no fixed limit on the number of data rows. There can be as many rows of data as excel permits.

2). Each day I get a new Master File so the processing daily will be a fresh start. In other words, I don't need to use the same file today which I used yesterday. A marker column in this scenario may not be needed. However, If the number of rows that do not get processed from the master file can be identified automatically after adding a column that states "Processed" or Pending" in any column in the Master File it would be perfect.

3). There can be a case when a Client File does not exist, though I'll ensure it is present. In such cases, it should simply identify such cases and carry on with the processing of the others available

Hope my answers clarify my request.

Do let me know if you have more questions. Apologies for the delayed response.

Thank you,
Spi

Hi Spi

Sorry, I would not even begin to know how to deal with a file this large.

Sorry, Maybe I misunderstood your question. The # of rows that need to be processed daily would be in the range of 1-1000 only. I can further process the request in parts if it causes an issue. Is this what you wanted to know?

A file that size I can deal with...

Sorry, I had completely misunderstood it earlier

Happy to know it can be dealt with...

Please let me know if you have any more queries.

Thanks....Spi

Hi Spi

I didn't say I could accomplish what you desire...I can work with the smaller file you described.

Oopps....My Bad Luck

Hi Spi

This Code is in the attached. It assumes the attached workbook is in the same folder as your Master File and Report Files. It further assumes that a Report File exists for each Report Name in the Master File. Let me know of issues...

Please Login or Register  to view this content.

Hi Spi

Your Thread is not displaying properly for me...this is just a test...