• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidate different excel files with dynamic ranges

D

dobi

New Member
I have a little more than 100 files coming in every month in which I have to collect the data and place it it one master file.
I came across Chandoo's tutorial link:
http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/

Everything works fine except that I wanted the additional function of being able to copy the dynamic range of each Excel monthly report.

I have attached 3 files.
vba-macro-to-copy file - This is the file chandoo made to consolidate the data into one sheet.
A.xlsx & B.xlsx - These are just 2 examples of the reports that I receive every month.

I think my problem is that I cannot determine on the scope of using the ActiveWorkbook to be able to use the LastRowInOneColumn function.

Can someone help in how I can use that function for the dynamic ranges of the individual Excel files?

Thank you
 

Attachments

  • vba-macro-to-copy-data-from-multiple-files (1).xlsm
    22.7 KB · Views: 5
  • A.xlsx
    37.6 KB · Views: 8
  • B.xlsx
    40 KB · Views: 6
Hi Dobi

I have a macro which does the same thing. Please find it attached and let me know if this is what you are looking for.

Regards,
JD
 

Attachments

  • Consolidator.xlsm
    29.1 KB · Views: 11
I'm getting errors when I use the 2 of my sample files.

I cannot post the error messages because it's not in English but I see something with DATA_Model, DATA_Category, P2_DONE, P4_CANCEL, P3_POSTPONE, DATA_Status, DATA_Document, DATA_Driven, DATA_Segment, DATA_Objective.

I placed the 2 sample files in a separate folder with the Consolidator file and edited the folder path for the 2 sample files.

Is there something I'm doing wrong?
 
Hi Dobi

I presume that you are adding the path of your sample files in "Change Path and header settings" tab. There is a section named - Enter path - and you have added the link in the place where I added the sample link.

Once you add the link, you have to click the button available in the same tab - Click to combine files.

Also, I believe that the column heading for both the sample sheets are same. It is a mandate rule.

The macro is working fine at my end.

Regards,
JD
 
Yes. I did use the path of the folder that only contains the 2 sample files.
I keep getting the same errors.
Am I suppose to edit my header settings or my headers?

I see the data pasted but the headers of the first file is pasted.
The second file's header only has one line and then the rest is pasted.
Problem with mine is that I see spaces in between the first file and the second file.

Anything else I should be changing?

Thanks
 
Hi Dobi

Please find the macro attached with the output. I have amended the sample files as well, because it contains header in 2 rows. I delete the header with chinese language. I dunno why you have kept column "A" blank in both the sample sheet without any reason. I deleted the blank column from the sample sheets. The sample sheets contains formulas which is throwing the above pop-up of data and all.

Regards,
JD
 

Attachments

  • Consolidator.xlsm
    71.7 KB · Views: 9
  • B.xlsx
    33.5 KB · Views: 3
The 2 sample files I used has a specific template they made.
I cannot alter it since the customers are already used to that.
In those files, column A is blank and there's actually more header files.
I wanted to just set the beginning cell and column for the copy and paste procedure.

I'm still getting the same results with your new Consolidator file.
Is it possible that my Office has some different settings that is getting me different results?

Just curious, are you actually setting the number of rows for each file to copy?
Or is it dynamic?
 
Last edited:
Hi Dobi

The errors "
DATA_Model, DATA_Category, P2_DONE, P4_CANCEL, P3_POSTPONE, DATA_Status, DATA_Document, DATA_Driven, DATA_Segment, DATA_Objective." are due to some formulas used in the sheets. If you use paste special these can be avoidable. The heading should be single and standard to get the expected result from the macro. I am not sure about that the setting, but it should not play a major part here. The only thing you need to check here is the RAW data you gonna insert in the Macro. The Data should be in proper and standard order to get the expected result out of the macro.

You have to concentrate on "Change Path and header settings" tab of the macro. It contains all the neccessary information to get the end result.

It wouldn't take much time to make your raw data workable and once it is standard you are ready to get the result.

Regards,
JD
 
Let me give this another try when I get to another computer, which will be in a few hours.

Thank you
 
Hi JD,

I'm still getting the same errors on another computer.
Is there something I'm missing?
I always do Paste Special (Values only) when I do copy&paste manually.
 
Hi Dobi

I find that your sample file consist of 2 heading columns. Please make it one for this macro to work on. You can run the macro and just avoid the above error msgs. You will get the end product. I did the same in the sample file I attached in the above thread.

Regards,
JD
 
Hi JD,

I cannot edit the header files because those files are set in that format. I can only work with those types of templates.

I edited some of chandoo's file and I was successful in finding the last empty row. However, I was only able to find up to a fully blank cell and the blanks underneath had a formula.

Is there a way I can just search for a blank value? Disregarding the formula?

Thanks
 
Hi JD,

I manage to find a way to get the first cell value that is blank.

Thank you.

I have other questions regarding Chandoo's file.
Not sure if it's alright to ask on this thread.
 
Hi Dobi

Please share the link and let us know where did you stuck with the file. We will try to help you out with it.

Regards,
JD
 
Hi JD,

Still the same files.

Still 2 functions I am looking for to add to Chandoo's file.
1. Paste the selection to another Workbook with a specified sheet.
2. Make a button instead of pressing F5 all the time.

I'm thinking if this is the right logic in how to do the first one.
Have a Workbook variable.
Once the selection has been made, set the designated Workbook's Sheet as the the ActiveWorkbook/Activesheet.
Paste.

Is that correct?

I don't know the correct syntax to access other workbooks.

Thank you.
 
Last edited:
Hi JD,

Thanks for the links. That would be good for the button part.

I'm having trouble finding out the syntax in how to read/write to another Workbook. I'm trying to paste to another brand new sample file.
Do you have any links about reading/writing to another workbook?

Thank you.
 
Hi Dobi

What exactly you mean about Read/Write. Is it copying the data from one sheet and pasting it into another?

Regards,
JD
 
Hi JD,

Actually I think I got it.
I have another task where it might be a bit more difficult for me to figure out the syntax and also the logic on how to do it.

While I'm pasting the data on a certain column, I have to paste a specific name that matches that file on a different column. I attached a sample file of what it should look like. It's complicated to explain.

Could you advise me on the logic and syntax?

Thanks
 

Attachments

  • sample.xlsx
    61.2 KB · Views: 3
Hi Dobi

It it is hard for you to explain, it will be more than that for us to understand your requirement!. I just fine data in sheet1 in your sample file and what to do with it is a Mystery for me as well.

Please try to make it clear what exactly your need is.

Regards,
JD
 
Hi JD,

Yeah sorry.
This is what I have managed so far.
I can copy and paste the cells starting from column C and so on.
However, for each file there is a name that I must paste on column A.

Example: The name Alex would be pasted for the first file. All way until the end of the copy range. The name Bob would be pasted for the second file.

These names are in the file on a specific cell. Those names would not be difficult to grab. My problem is how to paste these names according to their original files.

I hope that explanation was good enough. It's still hard for me to explain it clearly.

Thanks.
 
Hi Dobi,

I am still unclear with the way you are explaning me the concept. I believe you mean to say that the Alex data will be paste first and then next etc.. If that is the case I would recommend for you to add a column in each individual sheet and name the first and last cell of the data with their respective name Ex Alex and henceforth follow the same process. I am not sure if this is what you are looking for. Once all the individuals data get merged then you can easily identify with the name whose data belongs to whom.

Regards,
JD
 
Hi JD,

The names are already available on their respective files. The name Alex already belongs in that file that I will copy the data from.
What I'm thinking is how to paste the one set of data with their respective name in one step and not having to go through another loop.

I hope that clarifies it more. I might have to come up with another sample to make it clearer.

Thanks.
 
You must log in or register to reply here.
Back
Top