On Thursday, March 28, 2013 10:54:10 PM UTC-7, GS wrote:
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

Well, that's exactly where I wanted to get to.
I love the specifically active sheet during selection. I assigned a key stroke r to the macro which makes it perfect when you are the target.sheet after a copy, hit ctrl + r and you are back to the source sheet for more copies.

The single cell selection on target sheet is great!

Sorry for my 'deer in the headlights' lack of comprehension, has to be frustrating to you.

Thanks for the total bail out on this. Your name goes on the code where ever I share it from here.

Regards,
Howard