To copy data from different workbooks

[raja]

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

 

[Tom Ogilvy]

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.

 

[raja]

i get an error "Runtime error 9" - Subscript out of range

 

[raja]

i get an error "Runtime error 9" - Subscript out of range

kindly help me...

 

[Dave Peterson]

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]

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

 

[Tom Ogilvy]

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.

 

[raja]

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]

For that code to work, the sample# workbooks need to be open.