copy from one workbook to another?
 

what is the code for copying a range of rows and columns from one workbook
to different rows and columns in a different workbook? The last line of code
shown below results in "Runtime Error 1004, Application-defined or
object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith

copy from one workbook to another?
 

Hi Keith,

If I correctly understand your intention, try:

Set myRange1 = Workbooks(sWB1).Worksheets("Sheet1"). _
Range("A1").CurrentRegion.Columns(1)

Set myRange2 = Workbooks(sWB2).Worksheets("Sheet1"). _
Range("A1").CurrentRegion

myRange2.ClearContents

myRange1.Copy Destination:=myRange2(1)

Note that is is usually unnecessary and inefficient to make selections.

---
Regards,
Norman



"keithb" wrote in message
...
what is the code for copying a range of rows and columns from one workbook
to different rows and columns in a different workbook? The last line of
code shown below results in "Runtime Error 1004, Application-defined or
object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith

copy from one workbook to another?
 

take the parens off myrange2


myRange1.Copy Destination:=myRange2

You dequalify it as an object when you put the parens around it.

--
Regards,
Tom Ogilvy

"keithb" wrote in message
...
what is the code for copying a range of rows and columns from one workbook
to different rows and columns in a different workbook? The last line of
code
shown below results in "Runtime Error 1004, Application-defined or
object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith

copy from one workbook to another?
 

Thanks Norman, your solution works; however, I am left with a question:

In your statement:

myRange1.Copy Destination:=myRange2(1)

How is myRange2(1) different from myRange2 without the "(1)" suffix?

Thanks again,

Keith



"Norman Jones" wrote in message
...
Hi Keith,

If I correctly understand your intention, try:

Set myRange1 = Workbooks(sWB1).Worksheets("Sheet1"). _
Range("A1").CurrentRegion.Columns(1)

Set myRange2 = Workbooks(sWB2).Worksheets("Sheet1"). _
Range("A1").CurrentRegion

myRange2.ClearContents

myRange1.Copy Destination:=myRange2(1)

Note that is is usually unnecessary and inefficient to make selections.

---
Regards,
Norman



"keithb" wrote in message
...
what is the code for copying a range of rows and columns from one
workbook to different rows and columns in a different workbook? The last
line of code shown below results in "Runtime Error 1004,
Application-defined or object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith

copy from one workbook to another?
 

Hi Keith,

In copy operations, if the source range and destination ranges are
differently sized, a 1004 run-time error will be generated.

Resizing the destination range to its first cell (i.e
DestinationRange.cells(1). or, more succinctly, Destnation.Range(1))
obviates this potential problem. I say potential, because if , in your
example, the currentregion areas for the source sheet and the destination
sheet are identically sized , then my defensive synrax would still work but
would be unneceessary.



---
Regards,
Norman



"keithb" wrote in message
...
Thanks Norman, your solution works; however, I am left with a question:

In your statement:

myRange1.Copy Destination:=myRange2(1)

How is myRange2(1) different from myRange2 without the "(1)" suffix?

Thanks again,

Keith



"Norman Jones" wrote in message
...
Hi Keith,

If I correctly understand your intention, try:

Set myRange1 = Workbooks(sWB1).Worksheets("Sheet1"). _
Range("A1").CurrentRegion.Columns(1)

Set myRange2 = Workbooks(sWB2).Worksheets("Sheet1"). _
Range("A1").CurrentRegion

myRange2.ClearContents

myRange1.Copy Destination:=myRange2(1)

Note that is is usually unnecessary and inefficient to make selections.

---
Regards,
Norman



"keithb" wrote in message
...
what is the code for copying a range of rows and columns from one
workbook to different rows and columns in a different workbook? The last
line of code shown below results in "Runtime Error 1004,
Application-defined or object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith

copy from one workbook to another?
 

set myRange = Range("A1:Z30")
? myrange.Address
$A$1:$Z$30
? myrange(1).address
$A$1
? myrange(1,1).address
$A$1
? myrange(35).address
$I$2

--
Regards,
Tom Ogilvy


"keithb" wrote in message
...
Thanks Norman, your solution works; however, I am left with a question:

In your statement:

myRange1.Copy Destination:=myRange2(1)

How is myRange2(1) different from myRange2 without the "(1)" suffix?

Thanks again,

Keith



"Norman Jones" wrote in message
...
Hi Keith,

If I correctly understand your intention, try:

Set myRange1 = Workbooks(sWB1).Worksheets("Sheet1"). _
Range("A1").CurrentRegion.Columns(1)

Set myRange2 = Workbooks(sWB2).Worksheets("Sheet1"). _
Range("A1").CurrentRegion

myRange2.ClearContents

myRange1.Copy Destination:=myRange2(1)

Note that is is usually unnecessary and inefficient to make selections.

---
Regards,
Norman



"keithb" wrote in message
...
what is the code for copying a range of rows and columns from one
workbook to different rows and columns in a different workbook? The
last
line of code shown below results in "Runtime Error 1004,
Application-defined or object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =

ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith

copy from one workbook to another?
 

Hi Keith,

sheet are identically sized , then my defensive synrax would still work
but would be unneceessary.

should be:

sheet are identically sized , then my defensive syntax would still work but
would be unnecessary


The syntax might work but the typing leaves something to be desired!

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Keith,

In copy operations, if the source range and destination ranges are
differently sized, a 1004 run-time error will be generated.

Resizing the destination range to its first cell (i.e
DestinationRange.cells(1). or, more succinctly, Destnation.Range(1))
obviates this potential problem. I say potential, because if , in your
example, the currentregion areas for the source sheet and the destination
sheet are identically sized , then my defensive synrax would still work
but would be unneceessary.



---
Regards,
Norman



"keithb" wrote in message
...
Thanks Norman, your solution works; however, I am left with a question:

In your statement:

myRange1.Copy Destination:=myRange2(1)

How is myRange2(1) different from myRange2 without the "(1)" suffix?

Thanks again,

Keith



"Norman Jones" wrote in message
...
Hi Keith,

If I correctly understand your intention, try:

Set myRange1 = Workbooks(sWB1).Worksheets("Sheet1"). _
Range("A1").CurrentRegion.Columns(1)

Set myRange2 = Workbooks(sWB2).Worksheets("Sheet1"). _
Range("A1").CurrentRegion

myRange2.ClearContents

myRange1.Copy Destination:=myRange2(1)

Note that is is usually unnecessary and inefficient to make selections.

---
Regards,
Norman



"keithb" wrote in message
...
what is the code for copying a range of rows and columns from one
workbook to different rows and columns in a different workbook? The
last line of code shown below results in "Runtime Error 1004,
Application-defined or object-defined error."

Workbooks(sWB1).Activate
Set myRange1 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion.Columns(1)
myRange1.Copy
Workbooks(sWB2).Activate
Set myRange2 =
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Cu rrentRegion
myRange2.Delete
Set myRange2 = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
myRange1.Copy (myRange2)

Thanks,

Keith