Copy a range of cells from multiple worksheets in a folder , and paste them one after other in column wise for some range and row wise for other range with out replacing the existing data

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I Need to copy a column from many worksheets in a folder and paste them in master worksheet or any other column wise. I need the particular column to be copied and pasted in columns of master work book. For example I have 10 workbooks in a folder containing similar type of data. I need to copy column number 6 of all these worksheets and copied to master or a new workbook from column 1 to 10 in a way column 1 of new workbook should be column 6 of workbook 1 in the folder and column 2 of new workbook should be column 6 of workbook 2 in the folder and so on ...

    similarly

    And a range of cells like A1:A7 from all these workbooks to be copied row wise in another new workbook.

  • A few questions for clarification:

    Are the columns to be copied always in the first sheet of each source workbook?

    Is the range of cells to be copied always in the first sheet of each source workbook?

    What is the full path to the folder containing the source workbooks?

    What is the extension of the source workbooks (xlsx, xlsm)?

    Will the source files be the only files in that folder?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Yes the columns to be copied are always in sheet 1

    The range of cells copied also are in sheet 1 always

    Can we set the path of the folder as the path from where the code will be running

    Extension is .xlsx

    Yes the folder has only the work sheets and the worksheet with the code running

  • Place this macro in your Master workbook and make sure that it contains a sheet named "Master". Save the workbook as a macro-enabled file in the same folder as the source files. Run the macro. It will copy the columns to the Master sheet and create another workbook with the A1:A7 ranges from each source file.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Place this macro in your Master workbook and make sure that it contains a sheet named "Master". Save the workbook as a macro-enabled file in the same folder as the source files. Run the macro. It will copy the columns to the Master sheet and create another workbook with the A1:A7 ranges from each source file.

  • My apologies. The variable strPath should not be there. Thanks Dave, for picking up on that. Do the Image30 files have an "xlsx" extension?

    delete "strPath &"

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Can you attach a copy of one of the Image30 files that is creating the error?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I tested the macro below and it worked with no errors using the Image30 file you posted.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • See if this version works for you.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!