Cut/Paste Range to dynamically created worksheet

[ianquiksilver]

Hello all Excel'ers

I have a quick question to ask. I'm in the process of writing a While loop which would cut the first 3000 rows and paste them into a newly created worksheet. I know the code to create a new worksheet, but is there a way of automating the "cut first 3000 rows to *new* worksheet" on the fly? Because currently I can only paste a range to another worksheet that I refer to by name (for example) "Sheet2", "Sheet3" but what if I don't know beforehand how many worksheets will be created? .. Is there a way to target "last Worksheet"? Because then I just need to "create new worksheet, after current one" and then run the "cut & paste 3000 rows to Last Worksheet" "while the first worksheet has more than 3k rows".
I'm NOT asking for the full code, just how to target an automatically created worksheet dynamically
thanks so much for all your input and help, not to mention your time!

best regards!
Kris A

PS: my final aim with this algorithm would simply be to split a huge database into smaller sections via Worksheets, then getting a file saved from each sheet.

 

[Peter_SSs]

I don't know just what your While loop is doing so I just made one up. See if this heads you in the right direction.

Dim wsAct As Worksheet, wsNew As Worksheet
Dim i As Long
  
Set wsAct = ActiveSheet
i = 1
Do While i < 10000
  Set wsNew = Sheets.Add(After:=wsAct)
  wsAct.Rows(i).Resize(3000).Cut Destination:=wsNew.Range("A1")
  i = i + 3000
Loop

 

[My Aswer Is This]

Since you said you did want the whole script written.
Here is a script to do the first 300 rows.

Sub Test()
'Modified 4/19/2018 6:58 AM  EDT
Application.ScreenUpdating = False
Dim ans As String
ans = ActiveSheet.Name
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "New"
Sheets(ans).Rows("1:300").Cut Sheets("New").Rows(1)
Sheets(ans).Rows("1:300").Delete
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

If you wanted to just do 300 rows at a time and then the run the script again to do the next 300 rows:
Try this:

Sub Test()
'Modified 4/19/2018 7:15 AM  EDT
Application.ScreenUpdating = False
Dim ans As String
Dim anss As String
ans = ActiveSheet.Name
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "New"
ActiveSheet.Name = ActiveSheet.Name & ActiveSheet.Index
anss = ActiveSheet.Name
Sheets(ans).Rows("1:300").Cut Sheets(anss).Rows(1)
Sheets(ans).Rows("1:300").Delete
Application.ScreenUpdating = True
End Sub

 

[ianquiksilver]

Hello MrExcel MVP I still have your name somewhere as comments in my code from the last time you helped me out with an algorithm :D

First off, thanks a million for actually having written the whole code for me, I really was just asking for one small detail and aimed at figuring it out on my own, but I thank you for your most kind gesture nonetheless!

My issue is that I'm generally still writing very basically, not making use of the object way of doing things (defining variables and using their specific methods).
My question is, would "Rows(i).Resize()" be an alternative to using "Range("X:Y")" ?

As for the while I was thinking of, it would have been a case of "while first sheet > 3000 rows, skim the top 3000 into a new sheet". I have enough info now to blend your code with mine to make it work (in theory ^^). I'll be back in a bit with an update! :D
Thanks again so much for your help!

 

[Peter_SSs]

First off, thanks a million for actually having written the whole code for me,

Well, I didn't really write your whole code as you still have to write the appropriate Do ... Loop code but I wanted a structure to show you the repeated adding of a new sheet and referring to it without name - and the whole thing was only a few lines.

My question is, would "Rows(i).Resize()" be an alternative to using "Range("X:Y")" ?

Not really. Rows().Resize will refer to a number of worksheet rows whereas Range("X:Y") refers to a number of worksheet columns

I'll be back in a bit with an update!

OK, but I may not be on the forum for much longer right now nor very much for a few days. Still, I'll check back when I can to see if you need more help.

Thanks again so much for your help!

You are most welcome.

 

[ianquiksilver]

and big thanks to My Answer Is This! now I can't possibly miss! :D

 

[ianquiksilver]

thanks again for your detailed response!

as for the Rows().Resize() being equivalent to Range, I just used X and Y as go-to references of random values to represent rows as well. In my case its "A1:AJ3001" so in that case, would your "rows(1).resize(3000)" be the same as using "Range (A1:AJ3001)" assuming I dont need anything past column AJ? Its mainly to get a better handle of how "Resize()" works and how it can relate to other possible references.

And just FYI the loop I was thinking of was just "while Worksheets("Sheet1") > 3000 rows, skim top 3000 rows to new sheet".

Best regards!

 

[My Aswer Is This]

My script only copies 300 rows since I test all my scripts and did not want to create 3000 rows
I assumed you knew how to modify the code.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Peter_SSs]

as for the Rows().Resize() being equivalent to Range, I just used X and Y as go-to references of random values to represent rows as well. In my case its "A1:AJ3001" so in that case, would your "rows(1).resize(3000)" be the same as using "Range (A1:AJ3001)" assuming I dont need anything past column AJ?

Yes, pretty much the same. You could use Range("A1:AJ1").Resize(3000) to be equivalent to Range("A1:AJ3000")

And just FYI the loop I was thinking of was just "while Worksheets("Sheet1") > 3000 rows, skim top 3000 rows to new sheet".

OK, that's fair enough. To do that you would need to keep deleting the 3000 rows from the top (as MAIT's codes do) and then re-checking if there are still more rows to go. Unless you particularly want the rows to be gone from the original sheet, I would just get the code to determine the number of rows and suppose there was 7,850 (stored in the variable 'rws') I would use a For .. Next structure

For i = 1 to rws Step 3000

Next i

or the Do .. Loop like I had but with

Do Until i > rws

Unless you have very large data it probably won't be noticeable, but deleting rows is a (relatively) slow process which is why I stepped down the sheet rather than deleting the rows and always working at the top of the sheet.
In fact, if the goal is just to get the other sheets created, I would use Copy/Paste rather than Cut/Paste.