Error 1004 After I added a new sheet?

[elcolio]

I added another sheet to my workbook and now my macro buttons are messed up, even though I am referring to the worksheet by sheet number.

This is the problem function, it is supposed to pull a random percentage of the rows on the FromSheet, then copy them to a temporary hidden sheet, Sheet7, then clear the FromSheet and copy the selected rows back:

Sub PullRandom(FromSheet As Integer, Pct As Double)
Randomize
Dim SelRow As Range
Dim MyRows() As Integer
Dim numRows, percRows, nxtRow, nxtRnd, chkRnd, copyRow As Integer
  numRows = Sheets(FromSheet).Range("A" & Rows.Count).End(xlUp).Row - 1
   percRows = CInt(numRows * Pct)
    ReDim MyRows(percRows)
     For nxtRow = 2 To percRows
getNew:
      nxtRnd = Int((numRows) * Rnd + 1)
       For chkRnd = 2 To nxtRow
        If MyRows(chkRnd) = nxtRnd Then GoTo getNew
       Next
      MyRows(nxtRow) = nxtRnd
     Next
 
  For copyRow = 2 To percRows
   Sheets(FromSheet).Rows(MyRows(copyRow) + 1).EntireRow.Copy _
     Destination:=Sheets(7).Cells(copyRow, 1)
  Next
 
Sheets(FromSheet).Range("A2:IV65536").Clear
  For copyRow = 2 To percRows
   Sheets(7).Rows(copyRow).EntireRow.Copy _
     Destination:=Sheets(FromSheet).Cells(copyRow, 1)
  Next
Sheets(7).Cells.Clear
Sheets(FromSheet).Rows("1:10000").RowHeight = 13.5
Sheets(FromSheet).Select
insNumCol (percRows)
End Sub

I have 3 buttons that reference this sub, the first works perfectly, the second gives error 1004 at the Sheets(FromSheet).Select line, and the third works but is off by one sheet.

What I don't understand here is that I am referencing the sheets by sheet number. Shouldn't Sheets(8) be Sheets(8) every time? How would adding a sheet (Sheet20, if it matters) mess up this macro?

 

[rallcorn]

You might take a look at this. It helped me tremendously in understanding the 3 different ways to which a worksheet may be referenced in VBA.
http://www.mrexcel.com/forum/showthread.php?t=518208

 

[Mohammad Basem]

Please provide more details about the following:

1. You are referencing sheets by their index number, which means you are maintaining sheets order, and sheet7 is always the 7^th sheet from left with Index value of 7. Correct?

2. Are the three buttons calling the same procedure PullRandom? What is the code associated with each one?

Since you are refering to sheet by index number, adding a sheet could create a mess if it is inserted in the middle or at the beginning. For example, if sheet20 is added as the first (physical) sheet, its index will be 1 though its name is Sheet20 and sheet7 will be shifted to the right and becomes Sheets(8).

 

[elcolio]

Ah there's my problem! I was referencing Sheets(7), but should have been referencing Sheet7. Thanks!