Create a new sheet or new file every 5000 rows


Create a new sheet or new file every 5000 rows, using VBA. Case 2?????. How ?




I wouldn't do you much of a favour by doing your homework. And don't seriously believe your teacher wouldn't notice where you got your wisdom from, either.

You need to split the available rows in the worksheet into groups of 5000. This code will tell you the last used row in column A.

lastRow = Cells(Rows.Count, "A").End(xlUp).Row

The last row in each group will be 5000, 10000, 15000, 20000 - multiples of 5000. You get this with a loop like this. Try the code.

For R = 1 to lastRow Step 5000
    Debug.Print R
Next R

That's the last row of each section. The first row is row 1 for the first section and R-1 for each subsequent one. That gives you code like this:-

firstRow = 1
For R = 1 to lastRow Step 5000
    Debug.Print "Rows " & firstRow & ":" & R
    firstRow = R - 1
Next R

Presuming that your worksheet has 17,500 rows the last number the above loop will spit out is 15,000. Therefore you need to add one more line of code below the Next R.

Debug.Print "Last group rows = " & firstRow & ":" & lastRow

You can get the macro recorder to give you code how to copy the ranges defined by firstRow and R or lastRow to a new sheet. The easiest (and sleekest) way of handling that is to create a function into which you feed start and end rows and a sheet name and just call that function within your loop + once after the loop.

Actually, you can avoid the repetitive extra lines below the Next R by increasing the loop size like this.

For R = 1 to (lastRow + 5000) Step 5000

The disadvantage of this is that if your worksheet happens to have exactly 20,000 rows, or another number exactly divisible by 5000, the added 5000 will cause a blank sheet to be created. So, you need to weigh the pros and cons: You can have the repetitive rows or a test that prevents the blank sheet in one way or another.


Answer the Question

You must create an account to use the forum. Create an Account or Login