Create new Worksheet [and pasteAll, there-in]for each consecutive range of data copied

[GERALDRAHMAN]

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


Gerald

 

[JLGWhiz]

Copy this procedure to your standard code module. It will create new sheets after your existing sheets and name them. There will be five rows per sheet except the last sheet may only contain the number of rows remaining after dividing by five.

Sub batching()
Dim sh As Worksheet, lr As Long, i As Long
Set sh = Sheets("source")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
     For i = 1 To lr Step 5
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Batch " & i
        sh.Cells(i, 1).Resize(5, 1).EntireRow.Copy ActiveSheet.Range("A1")
    Next
End Sub

 

[GERALDRAHMAN]

Thanks JLGWhiiz

It works well

 

[JLGWhiz]

You're Welcome
Regards, JLG