Userform for input of single value and ranges

[Brutusar]

I am using a VBA code doing the value input directly in the code, but it is really slow when values has to be changed as you can imagine.

How can I modify this code so I can use input in a user form for some of the values? This is the code, it copies a range, and past it n times in a different sheet. What I am looking for is to select the range in a inputbox, then enter how many times the range should be pasted into either the same sheet, or in a different sheet.

Sub CopyPaste()
Application.ScreenUpdating = False

i& = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row
k& = 2

For j& = 1 To 26
Sheet1.Range("A1:B3" & i).Copy
Sheet2.Range("E" & k).PasteSpecial
Application.CutCopyMode = False
k = k + 1
Next

Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I do not understand your code.
Please explain in words what your wanting to do.
And what do you plan to do with the Userform

 

[Brutusar]

Hi,

Step by step:

1. In the userform, select a range in a sheet
2. In the userform, enter a number of times the selected range should be pasted in to...(next step)
3. Select in the userform; option A: the same sheet, or option B: a different sheet (enter sheet name in the user form.
4: In the userform, click button to copy/paste the range n times (as selected in step 2)

 

[My Aswer Is This]

The only way to select a range with a Userform Open is to open the userform as modeless.
Like this Userform1.show Modeless.

And if you manually select the range with your cursor.
You would need a button that says Selection.copy

Then you need a textbox to enter number of times to copy.
What is the name of this Textbox

And paste where on the sheet?

You said same sheet or other sheet. OK what other sheet?
Where do you plan to enter other sheet name.
And What are the names of these two Option Buttons
You said: option A or option B

See I need exact specific details like this.

And If I copy the Range("Sheets("Alpha").Range("A1:A 20")
You want this range pasted into The Range
Sheet("Alpha") Range("B1:B20")
Then I guess Range("B21:B41")

And on and on I guess.
Depending on number of times entered into Textbox Named NumTimes
Please provide exact specific details.
You said:

Then

 

[My Aswer Is This]

Use a Code like this:
You would need
1. A textbox to enter number of times to copy
2. Two option Buttons to decide sheet name.
3. A button to run script.

So look at this and modify control names as needed.

Private Sub CommandButton2_Click()
'Modified  6/5/2021  2:26:50 PM  EDT
Dim Lastrow As Long
If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then
MsgBox "You must select a Option Button"
Exit Sub
End If
If TextBox4.Value = "" Then MsgBox "You must enter a value in TextBox4": Exit Sub

Selection.Copy
    If OptionButton1.Value = True Then sn = "Alpha"
    If OptionButton2.Value = True Then sn = "Bravo"

For i = 1 To TextBox4.Value
    Lastrow = Sheets(sn).Cells(Rows.Count, "C").End(xlUp).Row + 1
    Sheets(sn).Cells(Lastrow, 3).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False

End Sub

 

[Brutusar]

Use a Code like this:
You would need
1. A textbox to enter number of times to copy
2. Two option Buttons to decide sheet name.
3. A button to run script.

So look at this and modify control names as needed.

Private Sub CommandButton2_Click()
'Modified  6/5/2021  2:26:50 PM  EDT
Dim Lastrow As Long
If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then
MsgBox "You must select a Option Button"
Exit Sub
End If
If TextBox4.Value = "" Then MsgBox "You must enter a value in TextBox4": Exit Sub

Selection.Copy
    If OptionButton1.Value = True Then sn = "Alpha"
    If OptionButton2.Value = True Then sn = "Bravo"

For i = 1 To TextBox4.Value
    Lastrow = Sheets(sn).Cells(Rows.Count, "C").End(xlUp).Row + 1
    Sheets(sn).Cells(Lastrow, 3).PasteSpecial xlPasteValues
Next
Application.CutCopyMode = False

End Sub

Hi, thanks for your code. Unfortunately, I get an error code saying: Invalid use of Me keyword

 

[My Aswer Is This]

Hi, thanks for your code. Unfortunately, I get an error code saying: Invalid use of Me keyword

Do you have a OptionButton1 and a OptionButton2

So the Option buttons have a name Like OptionButton1

 

[My Aswer Is This]

Change this line of code:
If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then

To this:
If OptionButton1.Value = False And OptionButton2.Value = False Then

You see I removed "Me"

 

[Brutusar]

Still getting an error msg, but a different one: "Expected: end of statement"

Change this line of code:
If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then

To this:
If OptionButton1.Value = False And OptionButton2.Value = False Then

You see I removed "Me

 

[My Aswer Is This]

Show me the code your using.
And you did not answer my question which was this:
Do you have a OptionButton1 and a OptionButton2