You are here:

Excel/Data to be filled in SECOND workbook from 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:
This code worked for me:

Change  Book1a.xlsm  to the name of your WB1
Change  Book2.xlsx  to the name of your WB2
Change  Sheet1 to the name of your sheet with the data in WB1
Change  Sheet2 to the name of your sheet that will received the data in WB2 and where you will write the name to retrieve in cell B2

Sub copydata()
Dim WB1 As Workbook, WB2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sName As String
Dim dataB As Range
   Set WB1 = Workbooks("Book1a.xlsm")
   Set WB2 = Workbooks("Book2.xlsx")
   Set sh1 = WB1.Worksheets("Sheet1")
   Set sh2 = WB2.Worksheets("Sheet2")
   sName = sh2.Range("B2").Value
   If Len(Trim(sName)) = 0 Then
     MsgBox "No Name specified in cell B2 of " & _
       sh2.Range("B2").Address(0, 0, xlA1, True)
     Exit Sub
   End If
   Set dataB = sh1.Range("B2", sh1.Cells(sh1.Rows.Count, "B").End(xlUp))
   If Application.CountIf(dataB, sName) = 0 Then
     MsgBox "Name specified: " & sName & " does not exist in " & _
       WB1.Name & " on sheet " & sh1.Name
       Exit Sub
   End If
   On Error Resume Next
   sh1.ShowAllData
   On Error GoTo 0
   sh1.UsedRange.AutoFilter Field:=2, Criteria1:=sName
   sh1.UsedRange.Copy sh2.Range("A1")
   sh2.Range("B1").Value = sName
   On Error Resume Next
   ActiveSheet.ShowAllData
   On Error GoTo 0
   sh1.AutoFilterMode = False
End Sub

The code was tested with your data as you show in your question and worked for me.

--
Regards,
Tom Ogilvy



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

QUESTION: Input file is WB1.xlsx Shhet1 & Output file WB2.xlsm Sheet2.

Where to paste the code. Is it Workbook Code Event?

ANSWER: Sanddeep

You would do Alt+F11 to get to the visual basic editor.  Make sure you workbook where you want the code is the active project on the left side (it is selected).  Then in the menu do Insert=>Module

paste the code in that module (this is a general/standard module).  

You should only put code associated with workbook level events in the Workbook Code module.

You need to manually run this code.    If you want to have the code run whenever you enter a value in cell B2 of Sheet2 in WB2.xlsm, then see the instructions below the original code (updated with the names you provided).

Sub copydata()
Dim WB1 As Workbook, WB2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sName As String
Dim dataB As Range
   Set WB1 = Workbooks("WB1.xlsx")
   Set WB2 = Workbooks("WB2.xlsm")
   Set sh1 = WB1.Worksheets("Sheet1")
   Set sh2 = WB2.Worksheets("Sheet2")
   sName = sh2.Range("B2").Value
   If Len(Trim(sName)) = 0 Then
     MsgBox "No Name specified in cell B2 of " & _
       sh2.Range("B2").Address(0, 0, xlA1, True)
     Exit Sub
   End If
   Set dataB = sh1.Range("B2", sh1.Cells(sh1.Rows.Count, "B").End(xlUp))
   If Application.CountIf(dataB, sName) = 0 Then
     MsgBox "Name specified: " & sName & " does not exist in " & _
       WB1.Name & " on sheet " & sh1.Name
       Exit Sub
   End If
   On Error Resume Next
   sh1.ShowAllData
   On Error GoTo 0
   sh1.UsedRange.AutoFilter Field:=2, Criteria1:=sName
   sh1.UsedRange.Copy sh2.Range("A1")
   sh2.Range("B1").Value = sName
   On Error Resume Next
   ActiveSheet.ShowAllData
   On Error GoTo 0
   sh1.AutoFilterMode = False
End Sub


-------------------
To trigger the code with the change event for sheet2, go to sheet2, right click on the sheet tab and select view code.  

Then paste in this procedure in the sheet module for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WB1 As Workbook, WB2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sName As String
Dim dataB As Range

If Target.Address = "$B$2" Then

   Set WB1 = Workbooks("WB1.xlsx")
   Set WB2 = Workbooks("WB2.xlsm")
   Set sh1 = WB1.Worksheets("Sheet1")
   Set sh2 = WB2.Worksheets("Sheet2")
   sName = sh2.Range("B2").Value
   If Len(Trim(sName)) = 0 Then
     MsgBox "No Name specified in cell B2 of " & _
       sh2.Range("B2").Address(0, 0, xlA1, True)
     Exit Sub
   End If
   Set dataB = sh1.Range("B2", sh1.Cells(sh1.Rows.Count, "B").End(xlUp))
   If Application.CountIf(dataB, sName) = 0 Then
     MsgBox "Name specified: " & sName & " does not exist in " & _
       WB1.Name & " on sheet " & sh1.Name
       Exit Sub
   End If
   On Error Resume Next
   sh1.ShowAllData
   Application.EnableEvents = False   
   sh1.UsedRange.AutoFilter Field:=2, Criteria1:=sName
   sh1.UsedRange.Copy sh2.Range("A1")
   sh2.Range("B1").Value = sName
   Application.EnableEvents = True
   ActiveSheet.ShowAllData
   On Error GoTo 0
   sh1.AutoFilterMode = False
End If
End Sub

You only need one set of code depending on how you want to execute the code.

--
Regards,
Tom Ogilvy



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

QUESTION: Slight modification would be required:
1. In WB2.xlsm Sheet2 when 1st value is punched in B2, it gives answer in row #1 also.
2. Thereafter, the values of row #1 remains 'same' even when B2 is punched with different value.

Requirement: Instead of B2, I would like to punch in B1
Values should get filled in A2:E1000000

Answer
Sanddeep

Since I don't know which version of the code you are using, I will modify both.  Hopefully this will do what you want.

Sub copydata()
Dim WB1 As Workbook, WB2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sName As String
Dim dataB As Range, r as Range
   Set WB1 = Workbooks("WB1.xlsx")
   Set WB2 = Workbooks("WB2.xlsm")
   Set sh1 = WB1.Worksheets("Sheet1")
   Set sh2 = WB2.Worksheets("Sheet2")
   sName = sh2.Range("B2").Value
   If Len(Trim(sName)) = 0 Then
     MsgBox "No Name specified in cell B2 of " & _
       sh2.Range("B2").Address(0, 0, xlA1, True)
     Exit Sub
   End If
   Set dataB = sh1.Range("B2", sh1.Cells(sh1.Rows.Count, "B").End(xlUp))
   If Application.CountIf(dataB, sName) = 0 Then
     MsgBox "Name specified: " & sName & " does not exist in " & _
       WB1.Name & " on sheet " & sh1.Name
       Exit Sub
   End If
   On Error Resume Next
   sh1.ShowAllData
   On Error GoTo 0
   sh1.UsedRange.AutoFilter Field:=2, Criteria1:=sName
   if sh2.Range("B2").Value = "" then
      sh1.UsedRange.Copy sh2.Range("A1")
      sh2.Range("B1").Value = sName
   Else
      set r = sh1.UsedRange
      set r = r.offset(1,0).Resize(r.rows.count - 1)
      r.copy sh2.Range("A2")
   End if
   On Error Resume Next
   ActiveSheet.ShowAllData
   On Error GoTo 0
   sh1.AutoFilterMode = False
End Sub


-------------------
To trigger the code with the change event for sheet2, go to sheet2, right click on the sheet tab and select view code.  

Then paste in this procedure in the sheet module for Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WB1 As Workbook, WB2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sName As String
Dim dataB As Range, r as Range

If Target.Address = "$B$2" Then

   Set WB1 = Workbooks("WB1.xlsx")
   Set WB2 = Workbooks("WB2.xlsm")
   Set sh1 = WB1.Worksheets("Sheet1")
   Set sh2 = WB2.Worksheets("Sheet2")
   sName = sh2.Range("B2").Value
   If Len(Trim(sName)) = 0 Then
     MsgBox "No Name specified in cell B2 of " & _
       sh2.Range("B2").Address(0, 0, xlA1, True)
     Exit Sub
   End If
   Set dataB = sh1.Range("B2", sh1.Cells(sh1.Rows.Count, "B").End(xlUp))
   If Application.CountIf(dataB, sName) = 0 Then
     MsgBox "Name specified: " & sName & " does not exist in " & _
       WB1.Name & " on sheet " & sh1.Name
       Exit Sub
   End If
   On Error Resume Next
   sh1.ShowAllData
   Application.EnableEvents = False   
   sh1.UsedRange.AutoFilter Field:=2, Criteria1:=sName
   if sh2.Range("B2").Value = "" then
      sh1.UsedRange.Copy sh2.Range("A1")
      sh2.Range("B1").Value = sName
   Else
      set r = sh1.UsedRange
      set r = r.offset(1,0).Resize(r.rows.count - 1)
      r.copy sh2.Range("A2")
   End if
   Application.EnableEvents = True
   ActiveSheet.ShowAllData
   On Error GoTo 0
   sh1.AutoFilterMode = False
End If
End Sub

--
Regards,
Tom Ogilvy  
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


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.