You can actually use application.inputbox to get a range.

Option Explicit
Sub CopyFromPasteTo2()

Dim rngF As Range
Dim rngT As Range

set rngf = nothing
on error resume next
set rngf = Application.InputBox("Enter from range", Type:=8)
on error goto 0

if rngf is nothing then
exit sub 'user hit cancel
end if

set rngt = nothing
on error resume next
set rngt = Application.InputBox("Enter goto range", Type:=8)
on error goto 0

if rngT is nothing then
exit sub 'user hit cancel
end if

rngF.areas(1).Copy _
destination:=rngT.cells(1) 'let excel resize the range.

End Sub

And excel will make sure that the user chose a range--you don't have to check
for a valid address or what workbook/worksheet should be used.

Gary''s Student wrote:

Here is a generic copy/paste posted in 2005 that polls the user for source
and destination. You could modify it to use fixed ranges or ranges specified
in cells:

Option Explicit

Sub CopyFromPasteTo()

Dim strF As String
Dim strT As String
Dim rngF As Range
Dim rngT As Range

strF = Application.InputBox("Enter from range", Type:=2)
Set rngF = Range(strF)

strT = Application.InputBox("Enter goto range", Type:=2)
Set rngT = Range(strT)

rngF.Copy rngT

End Sub

--
Gary's Student

"DavidH" wrote:

Im a macro novice who needs help with table-driven copy & paste macro.

I have several worksheets that have macros that copy a source range to a
destination range. Both the source range and the destination rage are named,
but may be on different worksheets within the same workbook. Thanks to my
companys adoption of Sarbanes-Oxley, any worksheets that contain macros are
always suspect and subject to a higher level of testing. To alleviate the
problem, Id like to design a generic copy routine that can be run from an
add-in or personal.xls. I envision having a table in the target worksheet
with these fields: source_range, destination_range, Paste_Values (e.g. an
indicator to tell the macro whether to paste or past values), Append_Below
(e.g. an indicator that instructs the macro to append the data to the bottom
of the destination range).

Im a macro novice and probably getting in over my head with this one. Does
this approach seem workable? Do you have any suggestions for making the
routine more flexible? Has anyone already done something like this?

Thanks in advance,

David


--

Dave Peterson