Hi all :)

I have two excel files for data(one is to retrieve data from outer source(file1) meanwhile the other one is for template(file2)) and 1 excel file for macro(file3). I would like to create a macro to copy data from the file1 to the template(file2) and save it as another name according to year(file4_year). I have found the code to copy and paste the data into the template however it takes data from the macro workbook.I don't know which part should I change to fix this problem so that it will take the data from the file1 instead.

Sub TransferData()
    Dim wkb As Workbook, wks As Worksheet, LastRow As Long
    Dim FilePath As String, FileName As String, excelFile As String
    Dim ws As Worksheet, blnOpened As Boolean
    FilePath = "C:\Documents and Settings\user\My Documents\FiST Mac\"
    FileName = "FiST_data_template.xls"
    Call ToggleEvents(False)
    excelFile = "Bloomberg.xls"
    Workbooks.Open "C:\Documents and Settings\user\My Documents\FiST Mac\" & excelFile
    Set ws = ThisWorkbook.Sheets("Sheet1")
        If WbOpen(FileName) = True Then
            Set wkb = Workbooks(FileName)
            blnOpened = False
            If Right(FilePath, 1) <> Application.PathSeparator Then
                FilePath = FilePath & Application.PathSeparator
            End If
                Set wkb = Workbooks.Open(FilePath & FileName)
                blnOpened = True
        End If
    Set wks = wkb.Sheets("Yearly")
    LastRow = wks.Cells.Find(what:="*", after:=wks.Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
    wks.Cells(LastRow, "C").Value = ws.Cells(7, "E").Value
    If blnOpened = True Then
        wkb.Close SaveChanges:=True
    End If
        Call ToggleEvents(True)
End Sub
Sub ToggleEvents(blnState As Boolean)
'Originally written by firefytr
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Function WbOpen(wbName As String) As Boolean
'Originally found written by Jake Marx
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
End Function

i have found the solution. here's the code for future reference;

Sub CopyCell()

    Dim wbk As Workbook
    Dim strFirstFile As String
    Dim strSecondFile As String
    strFirstFile = "C:\Documents and Settings\user\My Documents\FiST Mac\Data.xls"
    strSecondFile = "C:\Documents and Settings\user\My Documents\FiST Mac\FiST_data_template.xls"

    Set wbk = Workbooks.Open(strFirstFile)
    With wbk.Sheets("Year")
        .Range(.Range("A5:AJ5"), .Range("A65536").End(xlUp)).copy
    End With
    Set wbk = Workbooks.Open(strSecondFile)
    With wbk.Sheets("Yearly")
    Sheets("Yearly").[B65536:AK65536].End(xlUp)(2).PasteSpecial Paste:=xlValues
    End With
    wbk.SaveAs "C:\Documents and Settings\user\My Documents\FiST Mac\FISTdb.xls"

End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.