Copying from multiple worksheets to Output Sheet

  • I am trying to write an Excel VBA Macro that copies the contents of 2 worksheets and pastes them in an Output sheet vertically aligned below each other.


    In my attached file the Macro1 I have created first creates a header in "Output Sheet" with the name of the input sheet("Worksheet 1" and "Worksheet 2").

    It then locates the first row below the header of the input sheets. It determines the first column, first row and last row of the 2 worksheets in a loop. It then increases the row height and column width in the output range if necessary. It then attempts to copy the contents to "Output sheet".


    The Macro can be executed by pressing the Execute Macro button on Sheet4. The Output Sheet must be deleted before executing the Macro.


    However, the contents(including the buttons) are not being pasted correctly.


    Any suggestions on how to resolve this?


    File1.xlsm

  • I have resolved the error. I made a mistake in the following line of code.


    Set rng1 = ws1.Range(firstCol & firstRow & ":" & lastCol & lastRow)


    I put in lastColumn instead of lastRow.

Participate now!

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