Hello guys! How to set a random number according to the number of rows in a worksheet, randomly extract a non repeating row each time, and cut it from the original table to the new worksheet until all rows are extracted. And can be saved to the specified folder by folderpicker . please help me, thanks a lot.
According to your attachment an Excel basics VBA demonstration for starters :
PHP Code:
Sub Demo1() Dim V With [A1].CurrentRegion.Rows V = .Count .Parent.Copy .Item("2:" & V).Clear End With With Range("B2:B" & V) .Formula = "=RAND()" .CurrentRegion.Sort [B1], 1, Header:=1 .Clear End With V = Application.GetSaveAsFilename(, "Excel workbook,*.xlsx") If V <> False Then ActiveWorkbook.SaveAs V, 51 End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Thanks for your reply, but that isn't what I want. I have 25 rows in my table. I need to automatically generate 25 workbooks. Each workbook is the content of randomly selected rows.
I uploaded new zip file, the source worksheet have 5 rows , and I want extract 5 workbook with random row from source worksheet. If the source worksheet have 50 rows then extract 50 workbooks.
that is what I want.
I am waiting for your new reply, thanks a lot.
To help the explanation must be complete without anything to guess which is not the case here !
Or are you enough confident with your Excel / VBA skills to complete any helper code
in order to fit it to your need for what you forgot / misexplained ?
If Sheet1 has 10 rows of data, I need to randomly select a number from 1-10, cut the entire row which equal the random number in sheet1 export to a new workbook. I can repeat the operation until all the data in sheet1 are randomly selected. Can I make myself clear this time?
So what should happen just after the first row is exported to a brand new workbook ?
As I required to well elaborate each step …
Do it manually, take notes then once you understand your need it should be easy to describe it …
Imagine you need to ask some programmable robot to bring the milk bottle to the dining room table :
you just ask for 'bring the milk bottle' but you forgot to indicate
• to go to the kitchen
• to open the frigde door and after taking the bottle to close this door
• to move to the dining room
• where is the table …
The reason why I asked you to proceed manually and take notes then you should easily describe your need
at least if you understand how Excel works …
So for the moment, after the random new order, once the first row is exported to a brand new workbook, what is the next step ?
Last edited by Marc L; 05-01-2022 at 07:05 PM.
Reason: typos …
A VBA demonstration for a starting point (you may have to amend it for what you forgot / misexplained)
to paste only to the source worksheet module :
PHP Code:
Sub Demo1() Dim V, L&, R& With Application .DisplayAlerts = False .ScreenUpdating = False Workbooks.Add xlWBATWorksheet With [A1].CurrentRegion.Rows V = .Columns(1).Value [K1] = V(1, 1) For L = 2 To .Count R = Application.RandBetween(L, .Count) [K2] = V(R, 1) V(R, 1) = V(L, 1) .AdvancedFilter 2, [K1:K2], ActiveSheet.UsedRange.Rows(1) ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & L - 1, 51 Next Union(.Item("2:" & .Count), [K1:K2]).ClearContents End With ActiveWorkbook.Close .DisplayAlerts = True .ScreenUpdating = True End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 05-01-2022 at 11:11 PM.
Reason: optimization …
Dear sir, could I ask another question, how to change this code to export only one file per run, that is, I don't need to cycle and export all the data at one time. I only want to execute the code once when needed, generating only one file at a time.
Bookmarks