Copy User Selection from One Workbook to Another

[dehboy]

I've got this program that I want to have the user select a range in one workbook, close that workbook, and then paste that selection in the second workbook. This is VBA code for an excel document. Here is the selection code...

Function SelectARange(sPrompt As String, sCaption As String, oReturnedRange As Range) As Boolean
Dim frmSelectCells As ufSelectCells
Set frmSelectCells = New ufSelectCells
With frmSelectCells
.PromptText = sPrompt
.CaptionText = sCaption
If TypeName(Selection) = "Range" Then
.StartAddress = Selection.Address(external:=True)
End If
.Initialise
.Show
If .OK Then
Set oReturnedRange = .ReturnedRange
If oReturnedRange Is Nothing Then
SelectARange = False
Else
SelectARange = True
End If
Else
SelectARange = False
End If
End With
Unload frmSelectCells
Set frmSelectCells = Nothing
End Function

And here is what I thought would work...

goBack = 1
Do While (goBack <> 0)
Dim percentCopy As Range
If SelectARange("Select the month's cost / budget range of both '% year' AND '% actual'.", "Range Select", oRangeSelected) = True Then
addingRows = (oRangeSelected.count / 2)
Set precentCopy = oRangeSelected
goBack = 0
Else
If (MsgBox("You have pressed cancel, go back?", vbYesNo, "Automation Tip") = vbNo) Then
goBack = 0
Else
goBack = 1
End If
End If
Loop
otherBook.Close
Dim destRange As Range
Set destRange = Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row + addingRows - 1, ActiveCell.Column + 1))
destRange.Select
percentCopy.Copy destRange

Does anyone have anything that can help?

[ubentook]

What version of Excel is in use?
What happens when you run your code?
Is there a particular code line that fails?
Do you get an error message? If so what is the message?
Are you running the code from Excel or are you automating Excel from VB?
Also, the data type, scope and values of all variables should be shown.

[dehboy]

What version of Excel is in use?
What happens when you run your code?
Is there a particular code line that fails?
Do you get an error message? If so what is the message?
Are you running the code from Excel or are you automating Excel from VB?
Also, the data type, scope and values of all variables should be shown.

I'm using Excel 2003 Professional. When I run my code, the destination range is set correctly but nothing is copied or pasted to the destination range. I get no error messages. I'm running the code from excel...its a button click action. The scope of all variables shown but not declared can be assumed as global variables, and their types should be nothing radical (ie all are integers, strings, etc.). There are no arrays in this bit of code.

[ubentook]

It may be as simple as adding "Option Explicit" as the first line of code in the module (without the quote marks).
That line of code forces variable declaration/identifies misspelled variable names...

You are setting "precentCopy" to the selection but are
using "percentCopy" when copying.

I'm using Excel 2003 Professional. When I run my code, the destination range is set correctly but nothing is copied or pasted to the destination range. I get no error messages. I'm running the code from excel...its a button click action. The scope of all variables shown but not declared can be assumed as global variables, and their types should be nothing radical (ie all are integers, strings, etc.). There are no arrays in this bit of code.

[dehboy]

It may be as simple as adding "Option Explicit" as the first line of code in the module (without the quote marks).
That line of code forces variable declaration/identifies misspelled variable names...

You are setting "precentCopy" to the selection but are
using "percentCopy" when copying.

Good call on the percentCopy thing, I hate that there is no good way of figuring those. Unfortunately, though, I tried running the program with the change and it still didn't copy the range the user selects from one workbook to another. I do have the option explicit thing at the top of the module too.

As some more information, I pulled this user selection thing off someone's code online. They use a form they created to do all the selection thing, and there is a lot of code behind the form. I didn't copy paste that code cause I didn't think it was relevant and it was too big to copy/paste.

[ubentook]

Following is a simple example. You should be able to use it to modify your existing code.
"Range" or "Cells" without a workbook/sheet qualifier refers to the active sheet.
Close the workbook(s) After copy/paste.

'--
'Both workbooks must be open.
Sub CopyStuffBetweenWorkbooks()

Dim wbFirst As Workbook
Dim wb2nd As Workbook
Dim copyFrom As Range
Dim copyTo As Range

Set wbFirst = ActiveWorkbook
Set wb2nd = Workbooks("OtherName")

'Range and cells are qualifed with the workbook name and sheet!
'Notice the dots.
With wbFirst.Sheets(1)
Set copyFrom = .Range(.Cells(1, 1), .Cells(10, 3))
End With

With wb2nd.Sheets(2)
Set copyTo = .Range(.Cells(1, 3), .Cells(10, 5))
End With

copyFrom.Copy Destination:=copyTo
End Sub
'--

Good call on the percentCopy thing, I hate that there is no good way of figuring those. Unfortunately, though, I tried running the program with the change and it still didn't copy the range the user selects from one workbook to another. I do have the option explicit thing at the top of the module too.

As some more information, I pulled this user selection thing off someone's code online. They use a form they created to do all the selection thing, and there is a lot of code behind the form. I didn't copy paste that code cause I didn't think it was relevant and it was too big to copy/paste.