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
Somewhat complex code to copy data from one sheet to another 9
Code with hidden flaw ERROR 400 14
InputBox to name a range 2
Object variable or with Block variable not set 9
Need Help with a VBA subroutine 0
InputBox for column letter problem 2
Name the range AFTER copied to new sheet 6

Top