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.