Copy FROM - TO Object/Block variable not set

H

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
 
C

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
 
H

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
Click to expand...

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
 
C

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.
Click to expand...

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
 
H

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
Click to expand...

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.
 
H

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Similar Book to Book Code, one works other does not 11
Code with hidden flaw ERROR 400 13
Somewhat complex code to copy data from one sheet to another 9
InputBox to name a range 2
Object variable or with Block variable not set 9
InputBox for column letter problem 2
Combine three InputBoxes into one? 7
Name the range AFTER copied to new sheet 6

Top