Copy Range to new worksheet, consequetively

  • Hi All

    I would like a Macro that can copy values from 5 rows in a worksheet, starting from Row 1. Each "Batch" copied to a new worksheet should contain values from 5 rows, so first batch would be from Row 1 - Row 5 → copied to Sheet 1 , then Row 6 - Row 10 → copied to Sheet 2 , then Row 11 - Row 15 → copied to Sheet 3 ...... etc. This should be done until there are no values in the last row.

    So, the workbook would start off with only one worksheet, which could be named "source" for convenience and end up with many more sheets depending on how many rows are in the source worksheet, which can vary from time to time. There are 3 other "contraints/conditions that need to be considered 1. the number of columns in the "source worksheet may vary from 5 columns up to 30 columns. 2. The formatting that was applied to the values in the "source" worksheet need to be maintained. 3. The data posted should start in cell A1 for each new sheet.

    Thanks in advance


  • Re: Copy Range to new worksheet, consequetively

    Lastrow=sheets("Dest").Cells(Rows.Count, "A").End(xlUp).Row +1
    sheets("Source").range("A1:Z5").copy sheets("Dest").Cells(Lastrow,1)
  • Re: Copy Range to new worksheet, consequetively

    Hi, Walter ,

    maybe try it like this:


Participate now!

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