On Thu, 28 Mar 2013 17:02:41 -0700 (PDT), Howard wrote:

Some time ago I submitted code to copy FROM book_X TOO book_y with a problem I needed help with.

Claus fleshed it out to beyond my expectations, therefore I hung his 'shingle' under the sub name. It worked just fine.

I made changes to the variable names and the workbook names to be more intitive as what this relatively simple code does.

Somewhere I have crossed an i and dotted a t. Errors out with "400"

I am suspicious of the very last line of code, but seems to read okay to me.
Any combination of select a range FROM, single cell, A1:A5 A1;A10 errors out 400even if I select a range TOO that is identical to FROM.

I would think I could select any range FROM and a single cell TOO and it should work.

The code is in "Copy WkBook FROM" sheet module.
Both workbooks are saved, Macro-enabled.xlsm.
Both workbooks are open.

Thanks.

Howard

Sub Copy_Book_To_Book_Select_Ranges()
'Claus
Dim CpyRngFrm As Range
Dim CpyRngTo As Range

Set CpyRngFrm = Application.InputBox(Prompt:="Enter a Copy FROM Range.", _
Title:="Enter Copy FROM Range", Type:=8)
If CpyRngFrm Is Nothing Then Exit Sub
MsgBox CpyRngFrm.Address

Set CpyRngTo = Application.InputBox(Prompt:="Enter a Copy TOO Range.", _
Title:="Enter Copy TOO Range", Type:=8)
If CpyRngTo Is Nothing Then Exit Sub
MsgBox CpyRngTo.Address

Workbooks("Copy WkBook TOO").Sheets("Sheet1").Range("CpyRngTo").Value = _
ThisWorkbook.Sheets("Sheet1").Range("CpyRngFrm").V alue

End Sub

To expand a bit on what Gary wrote, in your last line, you use the term:

Range("CpyRngTo")

But CpyRngTo is a range object; it is NOT a string that is the name of a range; it is the actual range.


What you wrote is similar to something like Range("Cells(1,10), Cells(10,10)") where the proper syntax would be range(cells(1,10),cells(10,10))

Range("A1") is ok; range(CpyRntTo.Address) might be OK, Range("named_range") might be OK. Or, as Gary wrote, simply CpyRngTo