I would like to transfer data from one excel spreadsheet into another. Example: I have one excel "Examplesheet1.xls" filled with data in each column, say Name, Age, DOB. Another excel sheet "Examplesheet2.xls" with a form that needs to be filled in. But, we have 100 Names, Ages, DOB's, due to which I want to enter one Name in Examplesheet2, and it should pop in all the other details i.e., Age, DOB should pop in. If I clear the name and change to a different name, it should pop up that respective information. I have an algorithm in mind and that is as soon as I enter a name in the Name box in the Examplesheet2.xls, it shall search for this name among the 100 in the Examplesheet1.xls and when it matches, it will pull the Age, DOB in the respective boxes in the Examplesheet1.xls. But I am not able to know how to do it. Please help
Answers & Comments
Verified answer
Try the function VLOOKUP in your second file, it should do what you want (if I understand what you mean).
Yeah, it could be done. You would have to write a macro in Excel. You would need to go into Visual Basic Editor in Excel (Alt + F11), then in the Project Window you would want to open up the Microsoft Excel Object "ThisWorkbook" by double-clicking on it. That is where you want to put your code to copy the cells from one sheet to another. For your Object dropdown list select Workbook. For your Procedure dropdown list select Open. Then add code to Add a worksheet and copy the cells to that worksheet. By entering the code in that window, it will add the sheet and copy the cells when someone opens the workbook. You'll need to make a reference to the current date and entering a date that is entered on the worksheet to reference from. Below is some VBA code that I wrote that will add a new worksheet when you open the workbook and it's a new month. Just make sure that you keep the most current Worksheet tab at the far left and don't mess with the Date that is set in the worksheet's header. It will even add the date and tab to a new blank workbook if needed. The only thing you'd need to work on is the code for copying the data from one spreadsheet to another. I can't really tell you how to do the copy without knowing the cell range you are wanting to copy from and the range you are wanting to copy to. Private Sub Workbook_Open() 'Written By: Keith Sinders 'Date Written: 8/16/2007 Dim CurYear As Integer, CurMonth As Integer Dim NewMonths As Variant, FirstDay As Date Dim LastPeriod As Date Dim LastMonth As Integer, LastYear As Integer NewMonths = Array("Jan", "Feb", "Mar", "Apr", _ "May", "Jun", "Jul", "Aug", "Sep", "Oct", _ "Nov", "Dec") CurYear = Year(Now) CurMonth = Month(Now) FirstDay = CurMonth & "/1/" & CurYear If Sheets(1).PageSetup.RightHeader = "" Then With Sheets(1) .PageSetup.RightHeader = FirstDay .Name = NewMonths(CurMonth) & " " & CurYear End With End If LastPeriod = Sheets(1).PageSetup.RightHeader LastYear = Year(LastPeriod) LastMonth = Month(LastPeriod) If CurYear - LastYear >= 1 Then Sheets.Add With ActiveSheet .Move Before:=Sheets(1) .Name = NewMonths(CurMonth) & " " & CurYear .PageSetup.RightHeader = FirstDay End With ElseIf CurMonth - LastMonth >= 1 Then Sheets.Add With ActiveSheet .Move Before:=Sheets(1) .Name = NewMonths(CurMonth) & " " & CurYear .PageSetup.RightHeader = FirstDay End With End If End Sub If you have any questions, feel free to write me. PS - If you use my source code in any matter, make sure to give me credit for the work. Programmers tend to get upset if someone uses their work and tries to claim it as their own. It also looks like it cut off the "r" on RightHeader in a few places in case you were wondering why the three dots are there.
maybe this well help :)
http://www.excelforum.com/excel-general/578069-aut...