| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To copy data from different workbooks
i want to copy data from different workbooks..
Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
Dim rng as Range, rng1 as Range
set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
i get an error "Runtime error 9" - Subscript out of range
"Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
Which line?
It usually means that something you referred to doesn't exist. Do you have an open workbook named Sample.xls? Does that Sample.xls have a worksheet named Sheet1? You may want to be more specific with which line in which routine causes the error. raja wrote: i get an error "Runtime error 9" - Subscript out of range "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
no my workbook sample.xls is not opened ..
yes,sample.xls have the sheet named sheet1 i want to copy without opening the worksheets sample,sample1 "Dave Peterson" wrote: Which line? It usually means that something you referred to doesn't exist. Do you have an open workbook named Sample.xls? Does that Sample.xls have a worksheet named Sheet1? You may want to be more specific with which line in which routine causes the error. raja wrote: i get an error "Runtime error 9" - Subscript out of range "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
no my workbook sample.xls is not opened .. yes,sample.xls have the sheet named sheet1 i want to copy without opening the worksheets sample,sample1 "Dave Peterson" wrote: Which line? It usually means that something you referred to doesn't exist. Do you have an open workbook named Sample.xls? Does that Sample.xls have a worksheet named Sheet1? You may want to be more specific with which line in which routine causes the error. raja wrote: i get an error "Runtime error 9" - Subscript out of range "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
For that code to work, the sample# workbooks need to be open.
raja wrote: no my workbook sample.xls is not opened .. yes,sample.xls have the sheet named sheet1 i want to copy without opening the worksheets sample,sample1 "Dave Peterson" wrote: Which line? It usually means that something you referred to doesn't exist. Do you have an open workbook named Sample.xls? Does that Sample.xls have a worksheet named Sheet1? You may want to be more specific with which line in which routine causes the error. raja wrote: i get an error "Runtime error 9" - Subscript out of range "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
i get an error "Runtime error 9" - Subscript out of range
kindly help me... "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
To copy data from different workbooks
subscript out of range means that a name does not match the names used in
the code - the names provided by you for the workbook names. I used the generic Sheet1 since you didn't say what sheet - so that was an assumption - which you should naturally adjust to reflect the names of the actual sheet in each workbook. -- Regards, Tom Ogilvy "raja" wrote in message ... i get an error "Runtime error 9" - Subscript out of range kindly help me... "Tom Ogilvy" wrote: Dim rng as Range, rng1 as Range set rng =Workbooks("sample.xls") _ .Worksheets("sheet1").Range("A1:G4") rng.copy Destination:=ActiveCell set rng1 = Workbooks("sample1.xls") _ .Worksheets("Sheet1").Range("A1:G10") activecell.offset(rng.rows.count,0).Select rng1.copy Destination:=ActiveCell and so forth. a more general approach would be Dim rng as Range, i as Long for i = 1 to 10 set rng = Workbooks("Sample" & i & ".xls") _ .Worksheets("Sheet1").Range("A1").currentRegion rng.copy destination:= _ activesheet.cells(rows.count,1).End(xlup).offset(1 ,0) Next But that makes assumptions about what your data looks like and where it should go. In any event, the more you can handle your requirement in general terms, the shorter your code should be. -- Regards, Tom Ogilvy "raja" wrote in message ... i want to copy data from different workbooks.. Workbooks("sample.xls").Worksheets("sheet1").Range ("A1:G4").Copy Destination:=ActiveCell macro copied contents from sample.xls.. then i want to append data from the other workbook(sample1.xls) depends upon the range of the data.. this continues for n worksheets... wat should i do for that.. kindly help me.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need macros for copy data into new workbooks | Excel Discussion (Misc queries) | |||
| Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
| Copy data across workbooks | New Users to Excel | |||
| Copy data from workbooks | Excel Programming | |||
| text search between workbooks, copy data | Excel Programming | |||
Hybrid Mode
