Selecting dynamic row range in vba

[dshafique]

Hi everyone, i've been working on a macro which copies and pastes my table in another sheet 10 rows at a time. I'm trying to make a do while loop and make it dynamic, however, i can't seem to wrap my brain around how to set my range to more than row. for example, this is what I have so far:

 i = 1
k = Range("Q6") 'last row number
Do While i < k
Set Rng = Range("TableAll").Rows(i): Range("TableAll").ListRows (i + 9) ' having trouble with this line
  Range("TableAll").Rows(i).Copy
i=i+9
loop

I want to select rows i through i+9 (essentially every group of 10 rows
thanks in advance!

 

[My Aswer Is This]

Why not copy the entire table all at once like this:

Sub Copy_Table()
'Modified  7/16/2018  6:49:06 PM  EDT
Sheets(1).ListObjects("Table1").Range.Copy Sheets(2).Range("A1")
End Sub

 

[dshafique]

the table is too big for what we need. we need to paste every 10 rows to a different sheet. (we can manually select every 10, but since there are hundreds of rows, it takes all day)

 

[My Aswer Is This]

But you have not said where you want to paste the 10 rows.
So are you saying you want to just copy the 10 rows and stop?
So what first time copy just row 1 to 10 then stop?
Next time copy rows 11 to 21 and stop? And on and on?

 

[dshafique]

this is the loop in mind:
select first 10 rows in range
create a new sheet and paste it in A1
then select next 10 rows (11-21) and paste them into a new sheet etc
repeat unitl reached end of table

so basically yeah, copy rows 1-10, then 11-21, then 22-32, etc.

 

[My Aswer Is This]

Do you want the header row in the table copied each time.
If so would it be header plus 10 rows
And these new sheets. Are they given default names like sheet2 sheet3

See if you do not provide these type details I write a script then you think of these details and then come back and specify more. So it's best to provide all specific details at the beginning.

What is the name of the sheet with your large table.
And from your partial script it looks like your table name is TableAll
Is that correct.

And these new sheets will be added after the last sheet in the workbook is that correct?

 

[dshafique]

yes, the header row plus 10 rows, creates new generic sheets like sheet1, sheet2, etc. the new sheets will be added to the end of the spreadsheet. so loop 1 would be like:

select header plus rows 10 rows after
create a new sheet in the end of the workbook and paste it into A1

loop2: 
select header plus next 10 rows (rows 11-21)
create a new sheet at the end of the workbook and paste it into A1

repeat until reach end of table (as long as i+9<last row)

thanks

 

[My Aswer Is This]

And why did you not answer these two questions?

What is the name of the sheet with your large table?
And from your partial script it looks like your table name is TableAll
Is that correct?​

 

[My Aswer Is This]

I'm assuming the Table is named TableAll
And the Table is on the active sheet.
Run this script from the sheet with the LargeTable
The sheets were give names like New1 And New2
Using default names may cause problems with original sheet names already in the workbook.

This script may not copy every row in the table if there are not a even number of rows.
But it should copy nearly all of them
Your wanting 10 each time but it your table has 412 rows then it will only copy 400

Sub Copy_10_Rows()
'Modified  7/17/2018  12:56:57 PM  EDT
Application.ScreenUpdating = False
Dim ans As Long
Dim i As Long
Dim r As Long
Dim n As String
n = ActiveSheet.Name
r = 2
With ActiveSheet.ListObjects("TableAll").Range
ans = .Rows.Count
    For i = 1 To ans / 10
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "New" & i
        .Rows(1).Copy Sheets("New" & i).Cells(1, 1)
        .Rows(r).Resize(10).Copy Sheets("New" & i).Cells(2, 1)
        r = r + 10
Next
End With
Sheets(n).Activate
Application.ScreenUpdating = True
End Sub

 

[tonyyy]

This script may not copy every row in the table if there are not a even number of rows.
But it should copy nearly all of them
Your wanting 10 each time but it your table has 412 rows then it will only copy 400

The code in red will copy/paste the remaining rows...

Sub Copy_10_Rows()
'Modified  7/17/2018  12:56:57 PM  EDT
Application.ScreenUpdating = False
Dim ans As Long
Dim i As Long
Dim r As Long
Dim n As String
n = ActiveSheet.Name
r = 2
With ActiveSheet.ListObjects("TableAll").Range
    ans = .Rows.Count
    For i = 1 To ans / 10
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "New" & i
        .Rows(1).Copy Sheets("New" & i).Cells(1, 1)
        .Rows(r).Resize(10).Copy Sheets("New" & i).Cells(2, 1)
        r = r + 10
    Next
[COLOR=#ff0000]    If ans > r Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "New" & i
        .Rows(1).Copy Sheets("New" & i).Cells(1, 1)
        .Rows(r).Resize(ans - r).Copy Sheets("New" & i).Cells(2, 1)
    ElseIf ans = r Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "New" & i
        .Rows(1).Copy Sheets("New" & i).Cells(1, 1)
        .Rows(r).Copy Sheets("New" & i).Cells(2, 1)
    End If[/COLOR]
End With
Sheets(n).Activate
Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy