Select Random Row, Split Cells To Different Worksheets

Jul 26, 2009

I am trying to write a macro to:

1. Randomly selects 50 rows from 834 on "worksheet 1". There are only two cells per row.

2. Then, it clears "worksheet 2" and "worksheet 3".

3. From each selected row in "Worksheet 1", it splits the two cells and copies the results into two different worksheets, one column per sheet.

4. The rows in the two new worksheets should be congruent (i.e. same selected order from worksheet 1). Keeping the order the same is important.

This is what I have so far:

--------------------
Sub Macro1()
Dim rng As Range
Dim iRow As Long
With Rows("1:834")
Do
iRow = Fix(Rnd() * 834 + 1)
If rng Is Nothing Then
Set rng = .Rows(iRow)
Else
Set rng = Union(rng, .Rows(iRow))
End If
Loop Until rng.Areas.Count >= 50
End With

rng.Select

Selection.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End Sub
-------------------

View 9 Replies


ADVERTISEMENT

Select Random Cells In The Selection

Feb 8, 2009

I'm trying to select an x number of random selections within the selection.

Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them

This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?

View 9 Replies View Related

Select Random Cells From Multiple Sheets And Copy

May 10, 2014

I would like to select say 2 id from sheet1 and 2 names from sheet 2 randomly and copy to sheet3, to cells a and b,have seen various codes but none seem to fit the bill.

View 3 Replies View Related

Split Text Across Columns By Random Number

Feb 6, 2008

I am trying to split a cell into column by number. e.g. KOPAS LIMITED 30 ST CLAIR AVE W SUITE 1111 TORONTO ON
to

KOPAS LIMITED(new column)30 ST CLAIR AVE W SUITE(new column)1111 TORONTO ON. The numbers are random. My list is of 27000 rows long.

View 7 Replies View Related

Generate Random Numbers In Random Cells

Sep 18, 2009

I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.

View 2 Replies View Related

VBA Random Name Select

Dec 12, 2012

I have been thinking about a name selection tool that would automatically pick a few names for internal audit. I can handle the useform launch and other basics but here is what I see happening.

1. The empname! sheet contains a list of all the company employees, with employee number in column A, names in column B and an "X" or blank in column C.

2. The main sheet called auditmaster! contains nothing currently

3. When the code is ran the userform prompts the user to select how many individuals to audit by entering an integer in a textbox.

4. If the user selects 5 for instance, the code will run and will select 5 random employees for internal audit. It does this by first checking column A of the empname! sheet and seeing what the highest and lowest numbers are. This would create the range for the random function.

Next if there is an "X" in column C of empname! sheet, those employees numbers are excluded from the random choice.

Following this the function will select the appropriate number of individuals to audit using random again and place their names/number on the masteraudit! sheet.

The detail should be placed as the following, employee number goes to column F, name to column G. Starting with row 10. The code should also number the results 1 - however many were selected for audit in column E.

View 2 Replies View Related

Random Select.

Sep 14, 2008

I want to random select one of the following,red,yellow,blue,green,orange & white. (as text) in cells A2:A1001.

View 9 Replies View Related

Random Number Select

Feb 5, 2007

I need a macro that will select a random number between 1 and 6. ie select the number of a dice. It needs to input 1 random value into one cell and another random value into another. Like the dice values of a monopoly game.

View 9 Replies View Related

Select Cell At Random

Dec 10, 2007

I have an excel sheet attached that I would like to customise to become a random seat picker. I wanted it to select a random cell representing a seat and highlight that cell in someway (e.g. change the background colour or text colour) and display the data within in in a cell in the middle of the sheet.

I have used formula to achieve the later half of the problem but am unsure how to highlight the cell. my current solution uses f9 to refresh the data. Ideally I would like to attach the behaviour to a command button. I have attached the excell sheet and have highlighted the problems I am having.

View 3 Replies View Related

How To Select 50 Random Values From Sheet1

Dec 13, 2013

How to copy and paste 50 values randomly from column A in sheet1 to Column A in sheet2.

View 5 Replies View Related

Random Function On Select Range?

Mar 14, 2014

I would like to use the random function on a range of select numbers. I tried randbetween() but it won't work for me since my range of numbers are not in sequence (e.g. 1,2,4,6,7,8,9,10,21). Using randbetween() might result in numbers not within my range..

View 2 Replies View Related

Select Random Number From List

Oct 23, 2009

Say I have a list of 100 numbers (in column A). What formula would I use to have Excel list (in column B) 50 random numbers from the list of 100 or column A?

View 6 Replies View Related

Select 30 Random Items From A List

Apr 30, 2014

I've got a list of items (formatted alphanumeric) in column G starting at row 2. I would like a cell formula that would randomly select 30 items from this column and place them in column H starting at row 2. One caveat is that the list must remain static once created. If the Rand()function is used, it recalculates whenever the worksheet is updated so that might be a problem.

Cell formula to do this? A VBA solution would be OK as well.

View 9 Replies View Related

Split Worksheets

Jul 1, 2009

In column A of a spread sheet I have lots of entries in this format:

admin_staff_a_002
admin_staff_a_006
admin_staff_1_010
admin_staff_1_003
admin_staff_b_005
admin_staff_z_007
admin_staff_z_001

What I need to achieve is to have a different worksheet containing all the rows for admin_staff_a then another for admin_staff_1 etc.

I want to name each of the worksheets a, 1 etc so i'd remove the admin_staff bit and the _003 bit

View 3 Replies View Related

Select 81 Winners At Random From 5000 Entries

Nov 26, 2013

I am looking at ways to select 81 winners at random from a spreadsheet with 5000 + names on it. I have tried assigning each cell with a value between 1 and 81 and then removing the duplicates but i cant seem to get excel to pick 81 unique numbers, instead it will pick no number 1, or two number 70s.

I have managed to get a macro to pick 1 cell at random but cant get it to pick across the whole range of 5000.

View 1 Replies View Related

Select Random Rows And Display On New Sheet

Mar 30, 2009

I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.

Here is the original code from the previous post.

View 14 Replies View Related

Optimize Select Random Cell Value Without Duplicates Code

Apr 6, 2013

I have two worksheets. ws1 contains a large number of dates on column 3. ws3 contains around 20 dates on column 3.

I want to pick two dates randomly in ws1 that does not already exist in ws3.

Here is my working code, but it is really slow to process. How to optimize this code? Also I would like to add a function where if the macro is not able to find two random dates that aren't already in ws3 then exit loop.

VB:
Set ws1 = thisworkbook.sheets(1)
Set ws3 = thisworkbook.sheets(3)

lr = ws2.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Set rvis = ws2.Range("c2:c" & lr).SpecialCells(xlCellTypeVisible)
mynodate = 0

[Code] .....

View 7 Replies View Related

Random Select One Line Of Data For Each Person In A List

Dec 20, 2012

I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.

Code:
a1
b
c
d
e
f
g
h
i
j

2

3

4
REF_ID
NAME
DATE
BILL TOTAL

[Code] .....

View 1 Replies View Related

Split Worksheet Into Multiple Worksheets

Aug 12, 2009

This code splits a worksheet into multiple sheets (based on sales person in column A). But when it does it, it deletes the sheets and then re-creates them.

However this messes with my formulas I have linked to the split sheets and turns them into #REF! errors.

View 5 Replies View Related

Split Worksheet To Multiple Worksheets?

Mar 1, 2013

I would like to split a worksheet to multiple sheets based on a column header. On browsing through the forums I found the VBA code below.

My problem with is that the code automatically uses only the first column for spiliting into different sheets. But I would like to modify this so that it searches the first row for a matching header specified by me (Eg "Name" , which may be column 10)

Code:
Private Sub PagesByDescription()
Dim rRange As Range, rCell As Range[code]....

View 4 Replies View Related

Split Worksheet To Multiple Worksheets

Nov 8, 2008

I have a worksheet that has data in columns A to N. The Salesman’s number is in column A and I need to split this first worksheet in to separate worksheets in the same workbook for each salesman.

View 9 Replies View Related

Macro To Split Data Into Separate Worksheets

Oct 30, 2010

I have a macro below which splits the data in my workbook (Attached) into seperate workbooks on the basis of each change in data, and saves the new workbooks with the value available in column A. Everything else works perfectly with this code I just want the code to take the workbook name from Column B, not Column A as it is currently taking.

Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant

[Code] ......

View 9 Replies View Related

Split Large Range Into Separate Worksheets

Aug 28, 2009

The following code takes a large range of data (currently 20K+ rows) and breaks it out into separate worksheets. This takes a while, and I have been trying to insert a progress bar into this macro, but the progress bar goes in reverse (from 100 to 0) but the userform will not unload at the end.

Sub MoveCells()
Dim objBook As Workbook
Dim objSheet As Worksheet
Dim lngRowSpace As Long
Dim strName As String
Dim lngTimeRow As Long
Dim lngStartRow As Long
Dim lngInteration As Long
Dim strDataSheet As String
Dim boolError As Boolean
Dim counter As Integer
Dim PctDone As Single

View 9 Replies View Related

Split Of Main Data Based On Column Into New Worksheets?

Apr 4, 2014

spliting of main data By column criterias which start from column E1:L1 something like Pivot Table in new worksheet, but based on formula functions.Each worksheets have to based in one of this column D1:K1 headings. That also have to rapidly change with main data table.

View 9 Replies View Related

Split Date Into Multiple Worksheets Based On Data?

Mar 12, 2014

We are an office furniture company and we want to hold a master list of all stock. The columns we require are:

Product ID
Product Description
Product Grade
Product Price
Sold

The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.

The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.

View 3 Replies View Related

Split Data Based On 3 Columns Into Separate Worksheets?

May 22, 2014

I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.

creating a macro or implicating one into the workbook to run for future reporting.

View 13 Replies View Related

Split Data Into Multiple Worksheets Based Empty Row?

Jan 17, 2014

I have tried to split using KuTool and ASAP Tool but they dont work. They can only split base on #of row. Between each of the group of my data, there is a empty row that split them. I want to split every single row from A:H to a new workbook. Why A:H becuz from A1:A8, the A5 is empty. It has to be in range, specify by me, so if the condition is met as empty from A:H, then split from there and keep going on the whole spreadsheet.

View 8 Replies View Related

Split Data Into Separate Worksheets Based On Column C?

Apr 11, 2012

I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.

My VBA skills is limited to this particular task.

View 2 Replies View Related

Split Worksheet, Seperated By Blank Rows, Into Several Worksheets

May 26, 2009

how to split one large worksheet into several worksheets using VBA. The only criteria I have to go on in the large worksheet is the existence of blank rows between each chunk of data I want on it's own worksheet.

Please see the attached zip file. There are 39 chunks of similar (but varying in number of rows) data that are separated by 3 blank rows, so I want my workbook be split into 39 new worksheets. My goal is to get the data into list/table format so I can work with it more easily and export into Access etc.

Also, just out of curiosity, is it possible to convert a HTM file into CSV? The attached data starts life as a HTM file that I just convert to XLS

View 6 Replies View Related

Split Data Into Multiple Worksheets Based On Column

Feb 4, 2008

I have a huge Excel spreadsheet going into the 10 000 mark.

What I want to do is split the spreadsheet into multiple worksheets based on the Company column.

I've attached a small sample.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved