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