Excel Prompting User to Select a Range to Copy and Paste :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Prompting User to Select a Range to Copy and Paste
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

DRAGON00
Board Regular


Joined: 11 Dec 2002
Posts: 8


Status: Offline

 Reply with quote  

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.

Post Wed Dec 11, 2002 4:09 pm 
 View user's profile Send private message

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3112
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

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

Post Wed Dec 11, 2002 4:27 pm 
 View user's profile Send private message

DRAGON00
Board Regular


Joined: 11 Dec 2002
Posts: 8


Status: Offline

 Reply with quote  

Thanks Mud Face. Have a Great Holiday.

Post Wed Dec 11, 2002 4:34 pm 
 View user's profile Send private message

rafaaj2000
Board Master


Joined: 06 Dec 2002
Posts: 951
Location: Larache--Morocco
Flag: Morocco

Status: Offline

 Reply with quote  

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.


Post Wed Dec 11, 2002 7:29 pm 
 View user's profile Send private message Send e-mail

rafaaj2000
Board Master


Joined: 06 Dec 2002
Posts: 951
Location: Larache--Morocco
Flag: Morocco

Status: Offline

 Reply with quote  

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.


Post Wed Dec 11, 2002 7:31 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.