Userform input value to macro

[starsky]

Hello everyone,

I'm fairly new to VBA, but I have a macro that creates new worksheets, & copies the header from the main data sheet. This works fine, and the header row to C&P to the new sheets is specified in the macro.

What I want to do is have a userform assigned to the macro. The user will input the row (from the main data sheet) that should be used as the header row on the new worksheets.

At the moment this code is used to apply the header row on the new sheets. How do I get a value from the userform to row 1 of the new sheet?


'Copies row 3 of main data page
Rows("3:3").Select
Selection.Copy

'creates new excel worksheet & names it
Set oSheet = Worksheets.Add
With oSheet
.Name = "sheetname"
Rows("1:1").Select
ActiveSheet.Paste

Also, how do I code a Cancel button so that the macro is exited (i.e. not just unloading the form).

Many thanks.

[mdmackillop]

Hi Starsky,
Welcome to VBAX.
Is this what you're after?

Option Explicit
Private Sub CommandButton1_Click()
Copies
End Sub
Sub Copies()
Dim MDP As Worksheet
Dim oSheet As Worksheet
Dim Rw As Long
Set MDP = Sheets("MainDataPage")
Rw = Me.TextBox1.Text
'creates new excel worksheet & names it
Set oSheet = Worksheets.Add
With oSheet
.Name = "sheetname"
'Copies specified row of main data page to A1
MDP.Rows(Rw).Copy .Cells(1, 1)
End With
End Sub

[Kenneth Hobs]

Welcome to the forum!

When posting vba code, use vba code tags.

Private Sub CommandButton1_Click()
Dim oSheet As Worksheet

Rows("3:3").Copy

'creates new excel worksheet & names it
Set oSheet = Worksheets.Add
With oSheet
.Name = "sheetname"
'Copies row 3 of main data page
.Paste
End With
Application.CutCopyMode = False

End
End Sub

[starsky]

Thanks for the prompt response md. That looks like it might work, many thanks. Will try to incorporate it into the full macro.

Noted Kenneth!