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

  • UncleStringer

    The variable lCol refers to the Master sheet in the destination workbook. That sheet will start out blank so the first value of lCol will be 2. After the column from the first source workbook is copied, it will always have data in row 1 and lCol will increase by 1 with each column copied. At the end, the macro deletes column A which remains blank.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • As I mentioned previously, I don't get any errors when I run the macro. This line of code:

    Code
    1. LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    is simply looking for the last row with data in Sheet(1) of each workbook, so unless the sheet is blank, I don't know why it's generating an error. Perhaps it's best to do what Dave has suggested.

    It would be best to supply a sample workbook for both workbooks. The source workbook and the destination workbook.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Your original question and your sample workbooks do not match.


    Here is the code that reflects on the sample workbook you have provided.

    Your destination workbook is the workbook with the code and will be saved as a macro enabled workbook(.xlsm)

    Change the folder address in the code.

    There are two worksheets in the destination workbook. One named Columns and one named Rows.