Copy columns from multiple workbooks and paste into one worksheet

  • Hi,


    I need a macro, to copy specific column from multiple workbooks and then paste it in master workbook. All workbooks are in one folder with .xls extension.


    Every single file has the same style, with the same number of sheets, names etc.


    For example:
    Macro is in master woorkbook named "All data". Main worksheet where I need to paste columns is "List". Location of all files is C:\Documents.


    I need to copy data from every single file I choose:
    Worksheet "U_Emiss_t" from range D1:D191.


    Then I want to paste it in master workbook, in worksheet called "List". I need to start from column D, because in columns A, B and C I have some constant data.
    Macro should be able to find first empty column and then paste.


    It's a problem because I always used a macros with pasting rows, not columns, and my skills are on begginers level.


    I need this because I have more than 200 files to process (every single week).


    I would be happy with all kind of help, and if you need some more informations, feel free to ask.


    P.S. I can't upload an example file because of fragile data.

  • Re: I need to copy data from every single file I choose:
    So you plan on choosing 200 files, one file at the time?
    Would it not be easier to copy all the files to be copied from into a temp folder, like "C:\Temp" and just cycle through all of these without opening a window for each file so you can choose?
    When you say "specific column", does that mean always the same column, like Column D or a Column with a particular Header?.

  • Here is an example
    This should copy all 4th Columns From All Workbooks into Master providing that all, incl Workbook with code ("Master"?) are saved in one and the same Folder.
    Change references, like Sheet names, as required.


  • Works perfectly!


    Thanks a lot jolivanes. I just changed sheet names and add some small things.


    Now I plan to modify this code to copy from different sheets and paste it to different sheets.


    Example:
    From every worksheet "U_Emiss_t" to one worksheet "All_U_Emiss"
    From every worksheet "R_Emiss_t" to one worksheet "All_R_Emiss".


    Anyway you saved me tons of time. Thank you again.

  • Here is an example
    This should copy all 4th Columns From All Workbooks into Master providing that all, incl Workbook with code ("Master"?) are saved in one and the same Folder.
    Change references, like Sheet names, as required.


  • Two things.

    1) This thread is just about ready for old age pension benefits.

    2) Do not hijack someone else's thread. Start your own and refer to this Post if need be.

    When you start your own, explain in detail what is needed and if possible at all attach a workbook so people can see what it looks like. Like Dave mentioned, an empty Row where none should be for the code to work

  • Thank you for reply, I haven't really hijacked anything or have an intension. My requirement is very similar so i was just refering the code. I'm new to this so I didn't know how it works . Sorry if it's an offense or to be termed as hijack. And I'll take the suggestion to start a POST.

    Thanks a lot.