Import data from another workbook

[Chhaya]

I have created a workbook in Excel 2007 and in that i need to add data on a daily basis from another workbook, early i used to copy paste from one workbook to another, when i told my friend about it he told me why don't i use macro to copy data from one workbook to another, in the way it is being paste to the end of workbook in order to preserve the historical data i already got in another workbook. Does anyone know what would be the code for it.

[Harshini]

Before trying out any code make sure you give proper path for files which are being used because it might happen that if you have provided wrong path then your code won't work. Once you have done with it try to use the below code I'm sure with this code you will be able to import data from another workbook.

Sub Work(ByVal Tgt_Name As String, ByVal Src_Name As String)
    Dim TgtWbk As Workbook, SrcWbk As Workbook, WS As Worksheet
    If FileExists(Tgt_Name) Then
        ' Target exists so open it.
        Set TgtWbk = Workbooks.Open(Filename:=Tgt_Name)
        MsgBox "Kool : True"
    Else
        ' Target does not exist so create it.
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Tgt_Name
        Set TgtWbk = ActiveWorkbook
        MsgBox "Kool : False"
    End If
   'Open the Source Workbook
    Set SrcWbk = Workbooks.Open(Filename:=Src_Name, ReadOnly:=True)
   ' Run through all sheets in the source work ook
    i = 1
    For Each WS In SrcWbk.Worksheets
        If WksExists(TgtWbk, WS.Name) Then
            Status Src_Name, WS.Name, "Worksheet already exists in " & Tgt_Name & ", Skipped."
             MsgBox "Kool : True, No:" & i
        Else
            TgtWbk.Worksheets.Add After:=TgtWbk.Worksheets(TgtWbk.Worksheets.Count)
            TgtWbk.ActiveSheet.Name = WS.Name
            CopySheet WS, TgtWbk.ActiveSheet
            Status Src_Name, WS.Name, "Worksheet copied to Target."
            MsgBox "Kool : False, No:" & i
        End If
        i = i + 1
    Next WS
   ' Close the Source, no need to save
    SrcWbk.Close SaveChanges:=False
    Set SrcWbk = Nothing ' Correctly set the SrcWbk object to Nothing
    ' Do the same for the Target
    TgtWbk.Close SaveChanges:=True
    Set TgtWbk = Nothing
End Sub

[Chhaya]

Thanks for replying me , when i am trying to run my macro i am getting some kind of error message it seems that there is some issue with code. When i click on Run button the vba code view is then switched to sheet 2, rather then staying on sheet 1.

[Francesca]

Try to copy & paste the code in VBA where you wish to copy data from WorkBook("Blank1").Sheets("Sheet2") to WorkBook("Blank2").Sheets("Sheet1") which already stores some data:

Sub Copydata()
Dim WS_1 As WorkSheet, WS_2 As WorkSheet

Set WS1 =WorkBook("Blank1").Sheets("Sheet2")
Set WS2 =WorkBook("Blank2").Sheets("Sheet1")

WS1.UsedRange.Copy WS2.Cells.(Rows.Count, "A").end(xlUp)

End Sub