Results 1 to 5 of 5

Prompting User to Select a Range to Copy and Paste

This is a discussion on Prompting User to Select a Range to Copy and Paste within the Excel Questions forums, part of the Question Forums category; Please advise how I write a macro that would prompt a user to select a range (e.g. a1:e27) to copy ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    8

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    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.

    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

  3. #3
    New Member
    Join Date
    Dec 2002
    Posts
    8

    Default

    Thanks Mud Face. Have a Great Holiday.

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default

    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.



  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default

    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.



Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com