Append Data to Multiple Files based upon Criteria

[Topo]

I have been searching all over and have picked up pieces here and there and so far have the attached which does not really work. I am new to VBA but have done some basic programming in the past.

The scenario I have is that on a monthly basis I download a lot of data. The format is consistent each month. What I need to do is based upon the Cost Center, copy and append that to the bottom of a separate file and each Cost Center has its own file. The time consuming part is that I have about 35 different files I need to do this for. Each of the separate cost center reports are identical in format.

I do have a listing of what the file name is based upon the cost center. Would it be easier to add a column in that would add the file name to make the copying and appending easier?

What would be the easiest way to go about doing this?

Thank you all for your help.

[rvasquez]

Hello there,

How are the files named? For example do they look like 12345.xls?

Thanks!

[Topo]

The files names I am using are for Example: 12345 Department Name.xls

I could very easily add a column to the end and just vlookup it in there if it makes things easier.

[rvasquez]

Can you please attach a sample of a Cost Center's workbook?

thanks!

[Topo]

The attached is the basic template I have setup. I have a file like this for each of the ~35 Cost Centers. From the main template, I would like the information appended to the bottom of the Details tab. As you can see it is a straight copy paste, no formatting changes necessary. From there everything else is automatically calculated.

[rvasquez]

Alrighty,

I'm not sure this was the easiest way to do it, but I believe it will get the job done. I have designed a macro for you that first copied the unique CostCtr values from column C in the template workbook to column N. Then it loops through those values opening up the workbook whose name begins with that number in the filepath you specify. It then loops through the cells in column C again and if they match the current value in the CostCtr loop/CostCtr workbook open then it copies and pasted that cells row from column B to L into the CostCtr's workbook. It then closes and saves the workbook and moves to the next until it is finished.

To insert this code into your workbook:

1. Press Alt+F8
2. clear the macro name field and then type ExportCostCtr
3. Select the Create option
4. In between the Sub ExportCostCtr() and End Sub, copy and paste the below code

Please Login or Register  to view this content.

5. anything that appears in green is a comment left, hopefully to help you understand. Please read through the code to find the line of code

Please Login or Register  to view this content.

6. Above this line of code are directions to help you change the filepath name. You'll need to change this in order for you code to work properly.

7. Exit out of visual Basic
8. Right click on the Export shape in column A that you created and select assign macro
9. Select the ExportCostCtr macro and select okay.
10.Finally click on the Export Data button and hopefully watch it work.

Let me know if you have any questions.

Thanks!

[Topo]

Thank you for the very fast responses!

There are two things, and I am not sure how to fix them. It is working and opening the correct file based upon the cost center. However this section, that is setting the active workbook, how could I can I change this to make it make the "Details" tab the active one?

Please Login or Register  to view this content.

Finally, when it gets to closing and saving the workbook after copying, I am receiving a run time error 438 Object doesn't support this property or method. Not sure how to clear that either.

Thank you again for your help, it is amazing what can be done.


EDIT:

I was able to fix the Error 438, I had to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Topo]

Got it all to work now, this is the final coding below:

Please Login or Register  to view this content.

[rvasquez]

Glad you got it! Sorry I didn't get to this sooner but I think it's great you read through the code and were able to adjust it yourselF! Great job!

[Topo]

No need to apologize, Thank you for your help and the fast replies. I have been messing around with it and it is working great and will save me lots of time.

The only thing I am working on tweaking is if a cost center does not have a file already created, it right now gives an error. I am just trying to figure out how to ignore it and keep it going through.