| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to copy an image (or picture) from one workbook to a new sheetin another workbook | Excel Worksheet Functions | |||
| Excel-how to link source workbook to copy of destination workbook | Excel Worksheet Functions | |||
| Selecting data from 1 workbook to copy and paste to a 2nd workbook | Excel Programming | |||
| Need a macro to copy a range in one workbook and paste into another workbook | Excel Programming | |||
| Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
Hybrid Mode
