Copy FROM - TO Object/Block variable not set

[Howard]

The workbook "Copy To WKBook.xlsm" is a saved workbook.

When I enter "Range("A1:A5") into InputBox ColRngFrm prompt I get the error message per subject title.

I'd be happier if I could just enter the Column letter, say "D" and all the "A's" in the ColRngFrm would convert to D's for a range to copy from...

ColRngFrm = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

....and then enter the column letter in the ColRngTo for the column to copy to in the other workbook, say C, and get for a column to copy to...

ColRngTo Range("C:C")

Thanks.
Howard

Option Explicit

Sub CopyBookToBook()

Dim ColRngFrm As Range
Dim ColRngTo As Range

ColRngFrm = InputBox(Prompt:="Enter a Copy from Range.", _
Title:="Enter Copy from Column", Default:="The range to copy from")
If ColRngFrm = "The range to copy from" Or ColRngFrm = vbNullString Then Exit Sub

ColRngTo = InputBox(Prompt:="Enter a Copy to Range.", _
Title:="Enter Copy to Column", Default:="The range to copy to")
If ColRngTo = "The range to copy to" Or ColRngTo = vbNullString Then Exit Sub
MsgBox ColRngTo

Workbooks("Copy To WKBook.xlsm").Sheets("Sheet1").Range("ColRngTo").Value = _
ThisWorkbook.Sheets("Sheet1").Range("ColRngFrm").Value

End Sub

 

[Claus Busch]

Hi Howard,

try:
Sub CopyBookToBook()

Dim ColRngFrm As Range
Dim ColRngTo As Range

Set ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.",
_
Title:="Enter Copy from Column", Type:=8)
If ColRngFrm Is Nothing Then Exit Sub

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

Workbooks("Copy To
WKBook.xlsm").Sheets("Sheet1").Range("ColRngTo").Value = _
ThisWorkbook.Sheets("Sheet1").Range("ColRngFrm").Value

End Sub

You can enter the range e.g. as A1:A5 or you select the range in the
workbook.



Regards
Claus Busch

 

[Howard]

Hi Howard,



try:

Sub CopyBookToBook()



Dim ColRngFrm As Range

Dim ColRngTo As Range



Set ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.",

_

Title:="Enter Copy from Column", Type:=8)

If ColRngFrm Is Nothing Then Exit Sub



Set ColRngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _

Title:="Enter Copy to Column", Type:=8)

If ColRngTo Is Nothing Then Exit Sub

MsgBox ColRngTo.Address



Workbooks("Copy To

WKBook.xlsm").Sheets("Sheet1").Range("ColRngTo").Value = _

ThisWorkbook.Sheets("Sheet1").Range("ColRngFrm").Value



End Sub



You can enter the range e.g. as A1:A5 or you select the range in the

workbook.







Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks, Claus.

When I select OR type in the Range To InputBox ($H$1:$H$5) I now get the Circled Red X error box & 400.

Seems to be happy with the select or type in range in the Copy From Input box however.

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 25 Mar 2013 13:09:26 -0700 (PDT) schrieb Howard:

When I select OR type in the Range To InputBox ($H$1:$H$5) I now get the Circled Red X error box & 400.

Seems to be happy with the select or type in range in the Copy From Input box however.

I have testet the code without any error. Both workbooks were opened.

But you can also try another code where you only enter a column letter:

Sub CopyBookToBook2()

Dim ColRngFrm As String
Dim ColRngTo As String
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row

ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _
Title:="Enter Copy from Column", Type:=2)
If ColRngFrm = "" Or ColRngFrm = "False" Then Exit Sub

ColRngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _
Title:="Enter Copy to Column", Type:=2)
If ColRngTo = "" Or ColRngTo = "False" Then Exit Sub

Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _
Workbooks("Copy to WKBook").Sheets("Sheet1").Cells(1, ColRngTo)

End Sub

If you want, I can upload the workbook with the code.


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Mon, 25 Mar 2013 13:09:26 -0700 (PDT) schrieb Howard:






I have testet the code without any error. Both workbooks were opened.



But you can also try another code where you only enter a column letter:



Sub CopyBookToBook2()



Dim ColRngFrm As String

Dim ColRngTo As String

Dim LRow As Long



LRow = Cells(Rows.Count, 1).End(xlUp).Row



ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _

Title:="Enter Copy from Column", Type:=2)

If ColRngFrm = "" Or ColRngFrm = "False" Then Exit Sub



ColRngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _

Title:="Enter Copy to Column", Type:=2)

If ColRngTo = "" Or ColRngTo = "False" Then Exit Sub



Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _

Workbooks("Copy to WKBook").Sheets("Sheet1").Cells(1, ColRngTo)



End Sub



If you want, I can upload the workbook with the code.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hmmm, I have both workbooks open also. Not sure what little glitch I have stopping this from working.

I did notice I had a typo somewhere along the way, missing a space in the wkbook name...
Workbooks("Copy ToWKBook.xlsm") should be
Workbooks("Copy To WKBook.xlsm") which I corrected.

Thanks Claus. Yes, I would like to see the workbook.

 

[Claus Busch]

Hi Howard,

Am Mon, 25 Mar 2013 13:56:28 -0700 (PDT) schrieb Howard:

Thanks Claus. Yes, I would like to see the workbook.

please look here for the workbook "Howard1":
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191


You have to download it, because macros are not activated in the web
app.
If you enter a range, then do it without brackets. Try e.g. A1:A5


Regards
Claus Busch

 

[Howard]

Hi Howard,



Am Mon, 25 Mar 2013 13:09:26 -0700 (PDT) schrieb Howard:






I have testet the code without any error. Both workbooks were opened.



But you can also try another code where you only enter a column letter:



Sub CopyBookToBook2()



Dim ColRngFrm As String

Dim ColRngTo As String

Dim LRow As Long



LRow = Cells(Rows.Count, 1).End(xlUp).Row



ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _

Title:="Enter Copy from Column", Type:=2)

If ColRngFrm = "" Or ColRngFrm = "False" Then Exit Sub



ColRngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _

Title:="Enter Copy to Column", Type:=2)

If ColRngTo = "" Or ColRngTo = "False" Then Exit Sub



Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _

Workbooks("Copy to WKBook").Sheets("Sheet1").Cells(1, ColRngTo)



End Sub



If you want, I can upload the workbook with the code.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I just test flew the column only code and it works like magic. I like!!
I'll change the wording in the InputBoxes to be specific to a column letter and not a Range.

Thanks. Appreciate your help, always top notch!

Regards,
Howard