You are here:

Excel/Fill second workbook with data of FIRST workbook

Advertisement


Question
QUESTION: I have 2 workbooks WB1 & WB2 for input & output respectively & both kept opened.

WB1: Range: A1:E1085476


Sheet1

A   B   C   D   E
1   Date   Name   N1   N2   N3
2   14-Sep-14   ABCD   128.25   138.55   140.29
3   14-Sep-14   ABMN   128.25   138.55   140.29
4   15-Sep-14   ABCD   128.25   138.55   140.29
5   15-Sep-14   ABMN   128.25   138.55   140.29
6   16-Sep-14   ABMN   128.25   138.55   140.29
7   16-Sep-14   XYZAD   128.25   138.55   140.29
8   17-Sep-14   XYZAD   128.25   138.55   140.29


In WB2:
Output required as:

Sheet1

A   B   C   D   E
1   Date   ABCD   N1   N2   N3
2   14-Sep-14   ABCD   128.25   138.55   140.29
3   15-Sep-14   ABCD   128.25   138.55   140.29

Example 2:

Sheet1

A   B   C   D   E
1   Date   ABMN   N1   N2   N3
2   14-Sep-14   ABMN   128.25   138.55   140.29
3   15-Sep-14   ABMN   128.25   138.55   140.29
4   16-Sep-14   ABMN   128.25   138.55   140.29

In B2, I would be manually punching 1 name which is contained in B column of WB1. IF matched;
ANSWER: All related data (related to B2) should appear in A2:A1085476; C2:C1085476; D2:D1085476 & E2:E1085476

VBA required for this.
I am using Excel 2007.
Thanks for the help in advance.

ANSWER: Hi Sandeep,

I believe this code does what you want:

Option Explicit

Sub GetFromWB1()

  Dim WS1     As Worksheet      'Sheet 1 in WB1
  Dim oRow    As Long          'Sheet 1 in WB2 output row
  Dim Sname   As String         'Name string
  Dim Scell   As Range          'find cell
  Dim Scella  As String         'first find cell address
  
  oRow = 2
  
  Set WS1 = Workbooks("WB1.xlsx").Worksheets("Sheet1")
  
  Sname = Worksheets("Sheet1").Range("B2")
  
  With WS1.Range("B:B")
  
     Set Scell = .Find(Sname, LookIn:=xlValues)
     If Not Scell Is Nothing Then
        Scella = Scell.Address
        Do
         WS1.Range(Scell.Offset(0, -1), Scell.Offset(0, 3)).Copy Destination:=Cells(oRow, "A")
         oRow = oRow + 1
         Set Scell = .FindNext(Scell)
        Loop Until Scell Is Nothing Or Scell.Address = Scella
     End If
     
  End With

End Sub
__________________________________________________________

This code should be placed in a macro module in WB2, and Sheet1 in WB2 should be the active worksheet when you run it.

Let me know if any problems.

Damon

---------- FOLLOW-UP ----------

QUESTION: It must get 'auto-triggered' & fill the values.  Currently, for triggering, I have to manually do it. A 'Generate' button should trigger the code.

It retains: Old results even when a new value is punched in B2. 'Clear All Data' button may be needed to clear previous answer under each field.

Answer
Hi again Sandeep,

If you want the code to be automatically triggered by entering a new name in B2 enter this code into the worksheet's event code module:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$2" Then
     Application.EnableEvents = False
     GetFromWB1
     Application.EnableEvents = True
  End If
End Sub

To do this copy this code, then right-click on the worksheet's tab, select View Code, and paste into the Code pane.


If instead you would rather the code be triggered by a Generate button enter this code into the worksheet's event code module:

Private Sub CommandButton1_Click()
  Application.EnableEvents = False
  GetFromWB1
  Application.EnableEvents = True
End Sub

where CommandButton1 should be the name of your Generate commandbutton.


To have a button that will Clear All Data, add this code into the worksheet's event code module:

Private Sub CommandButton2_Click()
'  clear old data
  Range("A2", Cells(Rows.Count, "E").End(xlUp)).ClearContents
End Sub

Again, CommandButton2 should be the name of your Clear All Data commandbutton.


Finally, it wasn't clear to me how you wanted the old results to be retained.  Do you want new results inserted at the top above the old data?  Or perhaps the new data to be added after the end of the old data?  Either way there is a problem with the old data being modified by the introduction of a new name in B2 that is different from the name used to generate the other data in row 2.  Do you have a problem with the fact that the old data (just the first data row's Name column value) would necessarily be overwritten when you enter the B2 value for a new search?

Damon

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.