Writing VBA to Collect Data from a Different Sheet than the Active Sheet

Book1.xlsm

Attached is an example of what I need:

So I have a userform that is opened from the first sheet. When it is opened, the workbook switches to sheet 2.

I have the VBA for adding and deleting the data I want. I just need a real simple VBA code for pasting the values into sheet 1 instead of the active sheet which is sheet 2. Below is the vba code for what I have.

Private Sub CommandButton1_Click()
With ThisWorkbook.ActiveSheet
.Range("C4").Value = Me.TextBox1.Value
.Range("C5").Value = Me.TextBox2.Value
'Clear old data
.Range("A2", .Range("A" & Rows.Count).End(xlUp)).ClearContents

With .Range("A2").Resize(Me.TextBox1.Value)
.Formula = "=NORMINV(RAND(),$C$5,$H$7)"
.Value = .Value 'Convert formulas to values
End With
End With
Unload Me

End Sub


Essentially my values are pasted into sheet 2, but I want the random numbers to be generated in sheet 1 column A. How do i do this?

So I want to grab the values on the active sheet but I want to generate random data into a different sheet, sheet 1, not sheet 2.

Close. Now I am getting an error on the line ".range("A2"). Resize (Me.TextBox1.Value)

Private Sub CommandButton1_Click()
With ThisWorkbook.ActiveSheet
.Range("C4").Value = Me.TextBox1.Value
.Range("C5").Value = Me.TextBox2.Value

With Sheets("Sheet1")
.Range("A2", .Range("A" & Rows.Count).End(xlUp)).ClearContents
.Range("A2").Resize (Me.TextBox1.Value)
.Formula = "=NORMINV(RAND(),$C$5,$H$7)"
.Value = .Value
End With
End With
Unload Me

End Sub

So I have the data of average and standard deviation on sheet 2, which is the active sheet when the userform is open. I want to grab the values for average and st.dev. from cells C5 and H7 on the active sheet but I want to generate random values. I need the random number of values to be equal to the number is cell C4 of the active sheet. so all my data is on the active sheet (sheet 2), but I want to have the random values on (sheet 1).

Thank you.