VBA (Excel) to select random records from multiple tabs and paste them in another tab

[newnewbie]

I am learning VBA and need a little help with a macro that I am writing.
I have a worksheet with data that I split into multiple worksheets based on criteria in one column. E.g. Column A has unique usernames, I have a macro (found it online and tweaked) that splits the spreadsheet (Sheet1) into different tabs named by the criteria (lgold, gsmth, ppatty, etc.). So, as a starting point I have an excel file with 20-30 tabs. all named differently, with data. What I need done firther is:

1. delete tabs with fewer than 31 rows
2. select 5 rows from each remaining tab and copy them to a new tab, named “To Audit”. The 5 rows from each tab should not be the first 5, but either
a. random 5 rows, or
b. every Nth row….e.g. if “lgold” tab has 346 rows =346/5=69.2=select every 69th row and copy it to a new tab....whichever is easier

I need a push in the right direction as far as sequence and which functions to use. I am at the first few chapters of the “idiot’s guide to VBA” level right now….the above macro is a little too advanced for me to write yet.

Thank you all for help!

Lena

[ubentook]

You can get the bottom row from a particular column on a particular sheet by calling this function...
'--

.Function GetTheBottomRow(ByRef WS As Excel.Worksheet, Optional ByRef lngCol As Long) As Long
If lngCol = 0 Then lngCol = 1
GetTheBottomRow = WS.Cells(WS.Rows.Count, lngCol).End(xlUp).Row
End Function

'--
The Rnd function returns random numbers.
'--
You can add data from a row on one sheet to another sheet this way...
Worksheets("Sludge").Rows(1).Value = Worksheets("Mush").Rows(13).Value

[Geunther]

If you have not completed your task, reply, and I will assist you with your code. I write quite a bit of VBA, and this shouldn't be too difficult. The following code will step through each sheet in a workbook, deleting all sheets with less than data through column 5. Keeping your data consistent on the user sheets will be the most important part of the task. If the user data starts on row 5 with only one row of data, the sheet will be retained.

Good luck with your project


Sub testing()

Dim Wsht As Worksheet
Dim X As Long

For Each Wsht In Worksheets
X = GetTheBottomRow(Wsht)
Wsht.Select
If X > 4 Then
MsgBox ("lets keep this one")
' insert your code to randomize the the selection of rows
' move the rows to your evauluation sheet and evaluate
Else
MsgBox ("lets dump this one")
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
Next

End Sub