Prompting User to Select a Range to Copy and Paste

[DRAGON00]

Please advise how I write a macro that
would prompt a user to select a range (e.g. a1:e27) to copy and prompt again to select
where to paste.

Thank you.

 

[Mudface]

You could use something like the following (albeit with a bit of error-trapping thrown in). The user would type in the range to copy in the first input box (eg A1:E27 oe Sheet1!A1:A20 etc) and where they want it to be pasted to in the second input box. HTH.
<pre>
Public Sub CopyAndPaste()
Dim rngToCopy As Range
Dim rngToPaste As Range

Set rngToCopy = Application.InputBox("Enter range to copy", Type:=8)
Set rngToPaste = Application.InputBox("Enter range to paste", Type:=8)

rngToCopy.Copy rngToPaste

End Sub
</pre>

 

[DRAGON00]

Thanks Mud Face. Have a Great Holiday.

 

[Jaafar Tribak]

Hi,

The best approach is probably to create a userform (Copyfrm)with the following controls:

-2 RefEDITs ( RefEdit1 & RefEdit2)
-2 Labels (Label1 & Label2)
-2 CommandButtons (ComandButton1 & CommandButton2)

Place a commandbutton on sheet1 for e.g.
On clicking this button the userform will be loaded.

Place the following code in the Userform(Copyfrm)Module:



Private Sub CommandButton1_Click()
Dim tx1, txt2, SRngName, DRngName As String
Dim loc1, loc2 As Integer
Dim sht As Worksheet

On Error GoTo bad_range
Set sht = ActiveSheet
tx1 = RefEdit1.Text
tx2 = RefEdit2.Text

loct1 = WorksheetFunction.Search("!", tx1)
loct2 = WorksheetFunction.Search("!", tx2)
SRngName = Right(tx1, (Len(tx1) - loct1))
DRngName = Right(tx2, (Len(tx2) - loct2))

If Range(SRngName).Cells.Count < Range(DRngName).Cells.Count Then
MsgBox "Destination Range Can't Be Larger Than Source Range", vbCritical
RefEdit2.Value = ""
RefEdit2.SetFocus
Exit Sub
End If

Range(SRngName).Copy

Sheets(Left(tx2, (loct2 - 1))).Activate
Range(DRngName).Select
ActiveSheet.Paste

sht.Activate
Exit Sub
bad_range:
MsgBox "The selected range is not valid."

End Sub




Private Sub CommandButton2_Click()
Application.CutCopyMode = False
Unload Copyfrm
End Sub



Private Sub RefEdit1_Change()
RefEdit2.Enabled = True
RefEdit2.Text = ""
Label2.Enabled = True

End Sub



Private Sub UserForm_Initialize()
Copyfrm.Caption = "Copy Range."
Copyfrm.CommandButton1.Caption = "OK"
Copyfrm.CommandButton1.Default = True
Copyfrm.CommandButton2.Caption = "Cancel"
Copyfrm.CommandButton2.Cancel = True
Label1.Caption = "Select Source Range."
Label2.Caption = "Select Destination Range."
Label2.Enabled = False
RefEdit2.Enabled = False
RefEdit1.SetFocus

End Sub

In fact,in my opinion, the copying and pasting in the normal way is easier & quicker.However using a userform looks more professional and creating it is more challenging.The choice is yours.

Good luck.
Jaafar.

 

[Jaafar Tribak]

Hi,

The best approach is probably to create a userform (Copyfrm)with the following controls:

-2 RefEDITs ( RefEdit1 & RefEdit2)
-2 Labels (Label1 & Label2)
-2 CommandButtons (ComandButton1 & CommandButton2)

Place a commandbutton on sheet1 for e.g.
On clicking this button the userform will be loaded.

Place the following code in the Userform(Copyfrm)Module:



Private Sub CommandButton1_Click()
Dim tx1, txt2, SRngName, DRngName As String
Dim loc1, loc2 As Integer
Dim sht As Worksheet

On Error GoTo bad_range
Set sht = ActiveSheet
tx1 = RefEdit1.Text
tx2 = RefEdit2.Text

loct1 = WorksheetFunction.Search("!", tx1)
loct2 = WorksheetFunction.Search("!", tx2)
SRngName = Right(tx1, (Len(tx1) - loct1))
DRngName = Right(tx2, (Len(tx2) - loct2))

If Range(SRngName).Cells.Count < Range(DRngName).Cells.Count Then
MsgBox "Destination Range Can't Be Larger Than Source Range", vbCritical
RefEdit2.Value = ""
RefEdit2.SetFocus
Exit Sub
End If

Range(SRngName).Copy

Sheets(Left(tx2, (loct2 - 1))).Activate
Range(DRngName).Select
ActiveSheet.Paste

sht.Activate
Exit Sub
bad_range:
MsgBox "The selected range is not valid."

End Sub




Private Sub CommandButton2_Click()
Application.CutCopyMode = False
Unload Copyfrm
End Sub



Private Sub RefEdit1_Change()
RefEdit2.Enabled = True
RefEdit2.Text = ""
Label2.Enabled = True

End Sub



Private Sub UserForm_Initialize()
Copyfrm.Caption = "Copy Range."
Copyfrm.CommandButton1.Caption = "OK"
Copyfrm.CommandButton1.Default = True
Copyfrm.CommandButton2.Caption = "Cancel"
Copyfrm.CommandButton2.Cancel = True
Label1.Caption = "Select Source Range."
Label2.Caption = "Select Destination Range."
Label2.Enabled = False
RefEdit2.Enabled = False
RefEdit1.SetFocus

End Sub

In fact,in my opinion, the copying and pasting in the normal way is easier & quicker.However using a userform looks more professional and creating it is more challenging.The choice is yours.

Good luck.
Jaafar.