Ok, let's take it from the top...
Firstly, move the procedure from the sheet module to a standard module.
Secondly, revise the procedure something like this...
Sub CopyWkbToWkb_SelectRanges()
'Garry (GS)
Dim rngSource As Range, rngTarget As Range, lRows&, lCols&, sPrompt$
Const sTitle$ = "Copy Book to Book"
'Select range of values to copy in source wkb
Workbooks("wkbSource").Sheets("Sheet1").Activate '//edit to suit
' ThisWorkbook.Sheets("Sheet1").Activate '//optional alternative
sPrompt = "Select the Range to Copy"
Set rngSource = Application.InputBox(Prompt:=sPrompt, _
Title:=sTitle, Type:=8)
If rngSource Is Nothing Then Exit Sub
'Select first cell of range to copy values to in target wkb
Workbooks("wkbTarget").Sheets("Sheet1").Activate '//edit to suit
sPrompt = "Select the first cell of the target Range " _
& "to copy values to"
Set rngTarget = Application.InputBox(Prompt:=sPrompt, _
Title:=sTitle, Type:=8)
If rngTarget Is Nothing Then Exit Sub
'Transfer the values to the target range
With rngSource
lRows = .Rows.Count: lCols = .Columns.Count
End With 'rngSource
rngTarget.Resize(lRows, lCols).Value = rngSource.Value
End Sub
...where each sheet is specifically active during selection. Also, only
select the 1st cell on target sheet so there's no mistake on size of
the ranges matching correctly.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion