Select cells for copy on the fly
Posted by jp on October 26, 2000 10:32 AM
I have a macro that copies cells from one file to another.
Sub Macro1()
Windows("Book1.xls").Activate
'select area to copy (Step 1)
'Range("A1:A2").Select
Selection.Copy
Windows("Book2.xls").Activate
'select area to copy to (step 2)
'Range("E52:E53").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Is there a way to run the macro, then select the cells in each file when needed: Steps 1 & 2??? Like pausing and entering data...
Now I select the cells in first file and then select other file and select cells, then run macro as-is.
Thanks for any help you can provide!!!
Posted by Ben O. on October 26, 2000 11:36 AM
I'd recommend creating a form for selecting data. They can be very simple--consisting of only a RedEdit box, which lets the user select a range, and an OK command button. You can load the form in your macro with these lines, assuming your form's name is Form1:
Load Form1
Form1.Show
If the RefEdit box is called RefEdit1 and the OK button is called CommandButton1, use this code to select the range in the RedEdit box when the user hits OK:
Private Sub CommandButton1_Click()
Range(RefEdit1.Value).Select
Unload Form1
End Sub
After that code is executed, Excel will go back to your main macro that you loaded the form from.
-Ben
Posted by Ivan Moala on October 28, 2000 8:35 PM
As an alternative to Bens suggestion you could try
this;
Sub CopyFrom_To()
Dim Cpy 'Rg to copy
Dim Dest 'Destination Rg
On Error Resume Next
Set Cpy = Application.InputBox("Select range to copy", Type:=8)
On Error GoTo 0
If IsEmpty(Cpy) Then End
Cpy = Cpy.Address
On Error Resume Next
Set Dest = Application.InputBox("Select range to Paste to:", Type:=8)
On Error GoTo 0
If IsEmpty(Dest) Then End
Dest = Dest.Address(external:=True)
ActiveSheet.Range(Cpy).Copy Destination:=Range(Dest) 'Range("B11")
Set Cpy = Nothing
Set Dest = Nothing
End Sub
Ivan
Posted by Ivan Moala on October 28, 2000 8:56 PM
Modified version due to error
Sub CopyFrom_To()
Dim Cpy 'Rg to copy
Dim Dest 'Destination Rg
On Error Resume Next
Set Cpy = Application.InputBox("Select range to copy", Type:=8)
If Err.Number <> 0 Then End
On Error GoTo 0
Cpy = Cpy.Address
On Error Resume Next
Set Dest = Application.InputBox("Select range to Paste to:", Type:=8)
If Err.Number <> 0 Then End
On Error GoTo 0
Dest = Dest.Address(external:=True)
On Error Resume Next
ActiveSheet.Range(Cpy).Copy Destination:=Range(Dest) 'Range("B11")
Set Cpy = Nothing
Set Dest = Nothing
If Err.Number <> 0 Then
MsgBox Err.Number & " := " & Err.Description
End If
End Sub
Posted by jp on October 31, 2000 6:21 AM
Posted by jp on October 31, 2000 6:24 AM
Sorry for the followup confusion! Thanks for the help!! I tacked it to a button, and i makes this easir to keep track of
