Data transfer between two workbooks

[prathyush]

I have two workbooks book1.xls and book2.xls

I want to load data in sheet1 of book2 to sheet1 of book1.
This sheet1 of book2 has a table of 10 rows and 10 columns.

How to do that in VBA??

 

[prathyush]

It is just the syntax for specifying a named argument. Destination= will give an error.

Another example:

Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "Book2.xls"

Another improvement is needed..
In the above example since i know the no.of rows are 10 i gave it as Range("A1:E10")
Here i dont want to hardcode because it may change and i cannot keep on changing my formula.
Instead i want to copy till the blank row at the end..
how to do that??

 

[VoG]

If you want to copy everything try

Workbooks("Book2.xls").Sheets("Sheet1").UsedRange.Copy _
    Destination:=Workbooks("Book1.xls").Sheets("Sheet1").Range("A1")

 

[prathyush]

If you want to copy everything try

Workbooks("Book2.xls").Sheets("Sheet1").UsedRange.Copy _
    Destination:=Workbooks("Book1.xls").Sheets("Sheet1").Range("A1")

it works but it is copying entire data..
but my actual data starts from 5th row..i had some process related info in first four rows which is not required.

 

[VoG]

Try

Workbooks("Book2.xls").Sheets("Sheet1").UsedRange.Offset(4).Copy _
    Destination:=Workbooks("Book1.xls").Sheets("Sheet1").Range("A1")

 

[prathyush]

it works..
is there a way for column offset also??

 

[VoG]

How many columns do you want to offset by?

 

[prathyush]

here is my requirement

copy from 3 row 2nd column to nth row 5th column
here n is equal to empty row number - 1.dont the value of empty row number( i mean it will keep on changing)

 

[VoG]

Try

Dim LR As Long
With Workbooks("Book2.xls").Sheets("Sheet1")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B3:E" & LR).Copy _
    Destination:=Workbooks("Book1.xls").Sheets("Sheet1").Range("A1")
End With

 

[prathyush]

Try

Dim LR As Long
With Workbooks("Book2.xls").Sheets("Sheet1")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B3:E" & LR).Copy _
    Destination:=Workbooks("Book1.xls").Sheets("Sheet1").Range("A1")
End With

What if E is also not fixed????I mean if it is nth column rather than 5th column??

 

[VoG]

Try

Dim LR As Long, LC As Integer
With Workbooks("Book2.xls").Sheets("Sheet1")
    LR = .Cells(Rows.Count, "B").End(xlUp).Row
    LC = .Cells(3, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(3, 2), .Cells(LR, LC)).Copy _
    Destination:=Workbooks("Book1.xls").Sheets("Sheet1").Range("A1")
End With