0

Code:

Sub TestSelectRandom()
Dim howManyStudents As Integer
Dim howManyTimesRepeat As Integer
Dim headerRows As Integer
Dim lastRowNum As Integer
Dim lastColNum As Integer
Dim currentRange As Range
Dim dataSheet As Worksheet
Dim currentSheet As Worksheet
Dim randNum As Integer

howManyStudents = Application.InputBox("How many students would you like to select per sheet? Only put an integer (whole number).", "Question 1 of 3", , , , , , 1)
howManyTimesRepeat = Application.InputBox("How many sheets would you like? Only put an integer (whole number).", "Question 2 of 3", , , , , , 1)
headerRows = Application.InputBox("Do you have any header rows? If you do, put how many rows there are. If there are none, just put 0. Only put an integer (whole number).", "Question 3 of 3", , , , , , 1)

ActiveSheet.Name = "Data"
Set dataSheet = ThisWorkbook.Worksheets("Data")
lastRowNum = Cells(Rows.Count, 1).End(xlUp).Row
lastColNum = Cells(Columns.Count, 1).End(xlToLeft).Column
For sheetNum = 0 To howManyTimesRepeat
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetNum
    Set currentSheet = ActiveSheet
    For students = 0 To howManyStudents
        randNum = WorksheetFunction.RandBetween(headerRows + 1, lastRowNum)
        dataSheet.Cells(randNum, 1).EntireRow.Copy currentSheet.Cells(students, "A")
        dataSheet.Cells(randNum, 1).EntireRow.Delete
        lastRowNum = dataSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Next students
Next sheetNum
End Sub

Error:

Run-time error '1004': Application-defined or object-defined error

On line of this code:

dataSheet.Cells(randNum, 1).EntireRow.Copy currentSheet.Cells(students, "A")

The goal of this code is to take a list of students (and some amount of information I do not know), ask the user how many people they would like to select (X) , and how many times they'd like to repeat the process (Y). It is to gather X students randomly from the data source and put them into a sheet (named current Y) and repeat the process Y times.

Looking into historical questions with this error, I have verified that I am using a new module for the macro (not a worksheet-level macro). I've made a lot of random edits in the "for students" section of code, so it may not even be right now, just trying to get that error to go away.

How can I fix this?

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
Lucy Taylor
  • 11
  • 1
  • 2
    `For students = 0 To howManyStudents`: There's no row `0`, this should be `1`. – BigBen Aug 31 '21 at 15:46
  • You need to put that as a solution, as that resolved my issue. That was the problem. Thank you. – Lucy Taylor Aug 31 '21 at 16:05
  • I've voted to close the question as a typo. Side note: [use Long instead of Integer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Aug 31 '21 at 16:07

1 Answers1

0

EntireRow.Copy currentSheet.Cells(students, "A"), students can not 0 value !

abdomohamed
  • 134
  • 1
  • 5