Random generator giving script out of range

[sjtjr73]

I am having trouble trying to coping the random generated data that I collected. I get Subscript out of range. I know what it from. But I can't seem to over come it. I have a userform with a textbox and 2 button. The first button creates a new worksheet called FW19 or what is entered in the textbox. The other button runs the random data gather and copy it to a sheet. But I am having trouble calling the new sheet and I get the script out of range. Code below.

Option Explicit
Private Sub cmdBut1_Click()
Dim strFW As String
Dim LR As String
Dim ws As Worksheet
'' Creats Sheet name
strFW = UserForm1.txtbox1.Value

If strFW <> "" Then
  Set ws = ThisWorkbook.Sheets.Add
  End If
 '' Creats txt in cells Al and B1
 If strFW <> " " Then
 ws.Name = strFW
Range("$B$1").Value = txtbox1.Value
Range("$A$1").Value = "Fiscal Week"
End If
 

End Sub
''Random Generator
Private Sub CmdBut2_Click()
Dim LastRow As Long
Dim NbRows As Long
Dim RowList()
Dim I As Long, J As Long, K As Long
Dim RowNb As Long
   
    Sheets("INV_Data").Activate
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    NbRows = IIf(LastRow < 8000, LastRow * 0.003, 30)
    ReDim RowList(1 To NbRows)
    K = 1
    For I = 1 To NbRows
        RowNb = Rnd() * LastRow
        For J = 1 To K
            If (RowList(J) = RowNb) Then GoTo NextStep
        Next J
        RowList(K) = RowNb
        '' below is the line I am having trouble with. When it gives me the erroe I hover
        ''over("strFW") and it shows me what ws it wants to copy to
        Rows(RowNb).Copy Destination:=Sheets("strFW")(K + 2, "C")
        K = K + 1
NextStep:
    Next I
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Remove this line

Dim strFW As String

from the cmdBut1 code & put it just below Option Explicit. Then in the CmdBut2 code use

Rows(RowNb).Copy Destination:=Sheets(strFW).Cells(k + 2, "A")

 

[sjtjr73]

Still gets the same error. IT acts like it not seeing the new work sheet

 

[Fluff]

What is your code now?

 

[sjtjr73]

Option Explicit
Dim strFW As String


Private Sub cmdBut1_Click()
Dim strFW As String
Dim LR As String
Dim ws As Worksheet
'' Creats Sheet name
strFW = UserForm1.txtbox1.Value

If strFW <> "" Then
  Set ws = ThisWorkbook.Sheets.Add
  End If
 '' Creats txt in cells Al and B1
 If strFW <> " " Then
 ws.Name = strFW
Range("$B$1").Value = txtbox1.Value
Range("$A$1").Value = "Fiscal Week"
End If
 

End Sub
''Random Generator
Private Sub CmdBut2_Click()
Dim LastRow As Long
Dim NbRows As Long
Dim RowList()
Dim I As Long, J As Long, K As Long
Dim RowNb As Long

    Sheets("INV_Data").Activate
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    NbRows = IIf(LastRow < 8000, LastRow * 0.003, 30)
    ReDim RowList(1 To NbRows)
    K = 1
    For I = 1 To NbRows
        RowNb = Rnd() * LastRow
        For J = 1 To K
            If (RowList(J) = RowNb) Then GoTo NextStep
        Next J
        RowList(K) = RowNb
        '' below is the line I am having trouble with. When it gives me the erroe I hover
        ''over("strFW") and it shows me what ws it wants to copy to
        Rows(RowNb).Copy Destination:=Sheets(strFW).Cells(K + 2, "A")
        K = K + 1
NextStep:
    Next I
End Sub

 

[sjtjr73]

I forgot to take it out some were else. it works know. Thanks for the Help

 

[Fluff]

You're welcome & thanks for the feedback.

 

[sjtjr73]

One more question. I want to add name in Cell A2,B2. I know the code for and added it to the end before Unload Me is called. But the value did not get placed.

Option Explicit
Dim strFW As String


Private Sub cmdBut1_Click()
Dim LR As String
Dim ws As Worksheet
'' Creats Sheet name
strFW = UserForm1.txtbox1.Value

If strFW <> "" Then
  Set ws = ThisWorkbook.Sheets.Add
  End If
 '' Creats txt in cells Al and B1
 If strFW <> " " Then
 ws.Name = strFW
Range("$B$1").Value = txtbox1.Value
Range("$A$1").Value = "Fiscal Week"
End If
 

End Sub
''Random Generator
Private Sub CmdBut2_Click()
Dim LastRow As Long
Dim NbRows As Long
Dim RowList()
Dim I As Long, J As Long, K As Long
Dim RowNb As Long

    Sheets("INV_Data").Activate
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    NbRows = IIf(LastRow < 8000, LastRow * 0.003, 30)
    ReDim RowList(1 To NbRows)
    K = 1
    For I = 1 To NbRows
        RowNb = Rnd() * LastRow
        For J = 1 To K
            If (RowList(J) = RowNb) Then GoTo NextStep
        Next J
        RowList(K) = RowNb
        '' below is the line I am having trouble with. When it gives me the erroe I hover
        ''over("strFW") and it shows me what ws it wants to copy to
        Rows(RowNb).Copy Destination:=Sheets(strFW).Cells(K + 2, "A")
        K = K + 1
NextStep:
    Next I
 Range("A2").Value = "Record"
 Range("B2").Value = "Description"
 Range("C2").Value = "Location"
 
    
    Unload Me
End Sub

[CODE=vba]
should I add before close?

 

[Fluff]

Assuming you want those on the new sheet just add

Sheets(strFW)

before the range, like

Sheets(strFW).Range("A2").Value="Record"

 

[sjtjr73]

Fluff, Thanks for all the help. It working like a charm. Their only 2 things I am making the user do manually, because I could not figure it out. When the new FW sheet get made and data populates I making her opening the columns up they she can read everything and she might have to make sure the new sheets are in order.